To stop getting orphaned users.....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

To stop getting orphaned users.....

Author

Topic

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2001-06-27 : 05:56:13

when restoring to new server..

The output from this will produce scripts to create orphaned users on a fresh system with restored db's on(useful in DR type cases)

Print 'Add SS logins to SS'
select
'EXEC sp_addlogin ' + name
from
sysusers
where
issqluser = 1
and
name not in ('INFORMATION_SCHEMA','guest','dbo')

Print 'Give SS users access to the Database'

select
'EXEC sp_GRANTDBACCESS ' + name

from
sysusers
where
issqluser = 1 AND

name not in ('INFORMATION_SCHEMA','guest','dbo')

CREATE TABLE #tmpUsers(UN varchar(100),GN sysname,LN varchar(100),DBN sysname null ,UID int null ,SUSERID int null)
insert into #tmpUsers exec sp_helpuser

print 'Add NT Users to SS'

select distinct
'EXEC sp_grantlogin ''' + LN + ''''
from
#tmpUsers
WHERE
LN is not null and
LN <> 'sa'

Print 'Give NT Users Access to the database'

select distinct
'EXEC sp_grantdbaccess ''' + LN + ''',''' + UN + ''''
from
#tmpUsers
WHERE
LN is not null and
LN <> 'sa'

drop table #tmpUsers

Print 'Create the SQL Roles'
select
'EXEC sp_addrole ' + name
from
sysusers
where
issqlrole = 1 and
gid > 0

Print 'Add Role members'

CREATE TABLE #Rolemembers
( DBRole sysname , MemberName varchar(100),memberSID varbinary(1000))

insert into #Rolemembers exec sp_helprolemember

select
'EXEC sp_addrolember ''' + DBRole + ''',''' + a.name + ''''
from sysusers a join #Rolemembers b on a.sid = b.memberSID

drop table #Rolemembers

Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources