Restoring user permissions after refresh db

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 2000 Forums

SQL Server Administration (2000)

Restoring user permissions after refresh db

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-11-29 : 13:14:44

Hello

After refreshing a test database , I am running the following procedure , but I am not able to get back the windows authenticated user permissions. What should I do.

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
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
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

Thanks

Westley
Posting Yak Master

229 Posts

Posted - 2006-11-29 : 21:41:14

I don't think you will need to sync the Windows users, as its not required.

vaddi
Posting Yak Master

145 Posts

Posted - 2006-12-01 : 00:11:39

Hello

I create the following procedure before the database is restored/refreshed .

CREATE PROCEDURE dbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and isntuser=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
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
go

And run the dbo.sp_fixusers after the refresh is done.

But I donot get back the user permissions back .

What should I do.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources