TSQL : Users & Logins

TSQL for querying systems objects for database users & logins (2000 & 2005) -

--sql 2000 -- list database users
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) -- exclude internal sql acounts

--sql 2005 -- list database users
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts

-- sql 2000 -- list orphanned users for current database
select name 
from sysusers
where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 -- list orphanned users for current database
select name 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins

-- code to demonstrate sql users present in db, but not mapping to sql server logins

select * from sys.sysusers
      join sys.syslogins
  on sys.sysusers.name = sys.syslogins.name
    and sys.sysusers.sid <> sys.syslogins.sid

--sql 2000 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sysusers where islogin = 1
and uid not in (0,1,2,3,4) 
and sid not in (select sid from sys.syslogins) -- exclude mapped logins

--sql 2005 - users that need remapping to login following RESTORE
select 'sp_change_users_login ''update_one'',''' + name + ''','''+ name + '''' 
from sys.database_principals
where type = 'S' -- sql login
and principal_id not in (0,1,2,3,4) -- exclude internal sql acounts
and sid not in (select sid from sys.server_principals) -- exclude mapped logins