Источник: sql solace: TSQL : Users & Logins
—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