May 9, 2011 at 12:02 am
I have a Developer who needs permission to be able to backup but also restore a database from backups he's created of it.
I can easily achieve the backup rights by giving db_backupoperator permission, but the problem I'm having is with the restore rights.
If I get the permission right, he can restore the database but in the process he loses permission to the database through the sids.
What's the best way to allow a developer to do a restore of a database and maintain the initial security without having to resort to sysadmin privilege?
May 9, 2011 at 3:43 am
1) Grant CREATE DATABASE permision.
2) Recreate the login on the other server with same SID.
May 9, 2011 at 3:55 am
I knew that thanks, but how can I automate that as the user with the restore rights loses all permissions upon completing the restore.
May 10, 2011 at 10:50 pm
No one?
May 11, 2011 at 8:29 am
hi,
RESTORE permissions default to members of the sysadmin or dbcreator fixed server roles and the owner (dbo) of the database
im not really sure what you mean by when the developer loses permissions?
May 11, 2011 at 6:03 pm
He can restore the database with db_creator role but upon restoring, he loses access to the database because it doesn't re-connect his user with the login (SID). That's what I'm trying to get around.
Any ideas there?
May 12, 2011 at 1:46 am
does his login show when running:
sp_change_users_login 'report' ? maybe his login is becoming "orphaned"..
May 12, 2011 at 2:02 am
Yes that is exactly what is happening upon the restore. His login is becoming orphaned and he loses access to the database he's restored and I don't know how to get around that.
Any ideas?
May 12, 2011 at 2:27 am
i get around this by running
sp_change_users_login 'auto_fix', 'login_name'
go
sometimes i have to restore 50 plus databases i usually wrap it around a sp_MSforeachdb when i know i will have many orphaned users.
May 12, 2011 at 5:54 am
From Books Online:
"Only members of the sysadmin fixed server role can specify the Auto_Fix option."
May 12, 2011 at 6:11 am
Thats true, then the other way is to use the alter user command.
This can be used to repair orphaned users and works for both Windows and SQL Server logins
May 12, 2011 at 6:12 am
Thanks very much, I'll give this all a try tomorrow - http://msdn.microsoft.com/en-us/library/ms176060.aspx 🙂
June 19, 2011 at 3:42 pm
Post removed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 19, 2011 at 8:14 pm
Drop the login and recreate it with the same SID - that way, when the database is restored the SIDs match.
You can do this using sp_help_revlogin and passing the username parameter, or the create login statement specifying the SID.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2011 at 6:48 am
If it's the same database each time, why not create a job for the restore, with the login fix as a separate step, and then give the Dev permissions to run that single job?
It would certainly save you the permissions hassle.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply