Источник: 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 Read more: http://sqlsolace.blogspot.com/2007/04/tsql-users-logins.html#ixzz4MifcrxNP
