Reconnecting users

[MaxZak]

I've restored a database on another server where I've recreated all the users (SQL users and Windows users) with the same permissions of the original ones. The problem is that the sid of the newly created users isn't the same of the originals, so I wrote a little script to assign the right sid to the restored users.


exec sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go

/* SQL users */
update sysusers set sid=l.sid
FROM sysusers u INNER JOIN master..syslogins l
ON u.name=l.name
where (u.isntuser = 0) and
(islogin = 1) AND u.name=right(l.name,len(u.name))
go

/* Windows users */
update sysusers set sid=l.sid
FROM sysusers u, master..syslogins l
where (u.isntuser = 1) and
(islogin = 1) AND u.name=right(l.name,len(u.name))
go

/* dbo user */
update sysusers set sid=l.sid
FROM sysusers u, master..syslogins l
WHERE u.name='dbo' AND l.name='BUILTIN\Administrator'

exec sp_configure 'allow updates', '0'
reconfigure WITH OVERRIDE
go


Hope that's helpful. Max

 

[tlbroadbent]

SQL Server has a system stored procedure that performs this function. I recommend using the SP rather than doing ad hoc updates of system tables.

sp_change_users_login Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[MaxZak]

The problem is that sp_change_users_login doesn't allow me to reconnect Windows users, right?

 

[tlbroadbent]

True. sp_change_users_login only handles SQL logins. I should pay better attention to the posts.

There is a downloadable SP avaiable at Microsoft that will also handle NT logins. It does extensive validations. I prefer it to your code because of that. Your code does no validation and makes assumptions that may not be true on all servers.

See the following.

How to Resolve Permission Issues When a Database is Moved Between SQL Servers [ignore]

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240872

[/ignore] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.