August 4, 2009 at 1:01 pm
Hi,
We're using SQL 2005, the prod database gets backed up nightly, the backup file gets copied to the dev server and restored automatically. The problem is that the user ids created on prod lose their access right on the dev server.
Any ideas?
Aldo
August 4, 2009 at 1:32 pm
yes, that will happen any time you are restoring from one server to another. It's all about the difference between logins and database users and thier various SIDS. If you using 2005 you can use the alter user command to correct the logins, if using 2000 read up on sp_change_users_login in BOL. Reading through the rest of the Logins and users topics on BOL wouldn't be a bad idea as well for some better background knowledge.
-Luke.
August 4, 2009 at 1:44 pm
use the following in a script so that this will fix once its restored
sp_change_users_login 'report'
sp_change_users_login 'Auto_Fix', 'usr', 'pwd'
August 4, 2009 at 2:51 pm
Use this query to fill all orphan users on any database.
BEGIN
DECLARE @username varchar(25)
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
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END CLOSE fixusers
DEALLOCATE fixusers
END
SQL DBA.
August 5, 2009 at 5:43 pm
Thank you so much!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply