Keeping security on a database restore?

Question

Keeping security on a database restore?

jbalbo

SSCertifiable

Points: 7583
More actions
February 17, 2015 at 11:18 am

#398032

Hi
I have a database used in SSRS in which I get a backup daily and restore it to database "DATA"
The issue I come across is SSRS has a datareader user(ssrsuser) for access to the "report store" web where users get reports
access gets erased on each restore. Can I add something to my job to restore this user after each restore or keep the settings?
Thanks
Joe
declare @Bakpath varchar(80)
set @bakpath = 'C:\EData\ExtractedData\extract.bak'
Use Master
Alter Database [Data]
SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE DATABASE [Data] FROM DISK = @bakpath --location of .bak file
WITH REPLACE
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the issue is that the user [ssrsuser] on one server does not have the exact same sid as the same named user [ssrsuser] on the other server where you restore.

if you use sp_help_revlogin on the original server, and drop and recreate that user, with that script to recreate him, on the new server, the permissions will no longer get lost...

when the same user in sys.server_principals is tied to the newly restored sys.database_principals, and they have the same sid, that login will match the user and the permissions contained in the newly restored database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Thanks for getting back.

I get this backup from an outside source where we use canned software.

They send us a backup of the table data daily and the user doesn't exist on their copy

I read something about sp_change_users_login

Is this something I can add to my job after the restore to add the user ?

Thanks

Lowell SSC Guru Points: 323518 More actions · Answer 3

in that case, after the resotre, you need to fix the orphan; sp_change_users_login is the "old way to do it, which of course still works, but i prefer to try and use the newer ALTER syntax:

ALTER USER [ssrsuser] WITH LOGIN = [ssrsuser];

or the old way:

EXEC sp_change_users_login 'Update_One', 'ssrsuser', 'ssrsuser';

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

Great this worked!!!!

adding this onto my daily restore job..

Not sure if its the best way ???

thank you

CREATE USER [SSRSUser] FOR LOGIN [SSRSUser] WITH DEFAULT_SCHEMA=[dbo]

EXEC sp_addrolemember 'db_datareader', 'SSRSUser';

DeWayne_McCallie Hall of Fame Points: 3617 More actions · Answer 5

Or use this script if you have multiple orphaned logins to fix and don't want to do it one at a time:

USE [DBName]

--Must run this in the context of the db that has the orphaned users to auto_fix...

DECLARE @usrname VARCHAR(100) ,

@command VARCHAR(100)

DECLARE crs INSENSITIVE CURSOR

FOR

SELECT name AS username

FROM sysusers

WHERE issqluser = 1

AND ( sid IS NOT NULL

AND sid <> 0x0

)

AND SUSER_SNAME(sid) IS NULL

AND name <> 'dbo'

ORDER BY name FOR READ ONLY

OPEN crs

FETCH NEXT FROM crs

INTO @usrname

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @usrname

SELECT @command = ' sp_change_users_login ''auto_fix'', '''

+ @usrname + ''' '

PRINT @usrname

EXEC( @command)

FETCH NEXT FROM crs

INTO @usrname

END

CLOSE crs

DEALLOCATE crs