Transfering Security Between Instances

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Transfering Security Between Instances

Author

Topic

Analyzer
Posting Yak Master

115 Posts

Posted - 2008-03-27 : 12:20:15

When restoring DBs to other environments the SIDS don't transfer causing access problems, and when I use a SID scripting method and deploy on the target environment SQL says the login already exists.

I usually end up with the cumbersome process of explicitly removing the logins and re-applying - how to other DBA's get round this?

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-03-27 : 12:27:12

You can run this against your restored database and this will fix the users.

BEGIN

DECLARE @username varchar(255)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

IF @@FETCH_STATUS <> 0 --no users to fix but set dbo just incase
BEGIN
EXEC sp_changedbowner 'sa'
END

WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
BEGIN
EXEC sp_changedbowner 'sa'
END
ELSE
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
END
FETCH NEXT FROM fixusers
INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers
END

Analyzer
Posting Yak Master

115 Posts

Posted - 2008-03-28 : 05:35:41

Thanks - i will test immediately

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-28 : 09:21:55

Use this:

http://support.microsoft.com/kb/246133

We always use this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources