July 13, 2010 at 9:00 am
Hi,
I have a job in my dev server which restores a database from prod to dev daily and fixes orphaned users using the following script.
use [DATABASE NAME]
go
CREATE PROCEDURE dbo.sp_fixusers
AS
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
go
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>'
go
Exec sp_fixusers
go
drop proc sp_fixusers
go
EXEC sp_change_users_login 'report'
But the job fails if the login does not exist in the dev server. I am manually creating the logins and fixing the orphaned users if the job fails. But my client wants me to move logins through the job itself if they doesn't exist in the dev server.
Can anyone please provide me a solution
July 13, 2010 at 9:52 am
Do u want to keep the rights/permissions from the prod server to the dev server or u want the rights what ever in dev server remains same?
July 14, 2010 at 4:56 am
Sumit,
Thanks for the immediate response. I don't want to move the rights along with the login to dev server.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply