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.
| 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
|
|
|
|
|
|
To stop getting orphaned users.....