DBO permissions on database replaced during RESTORE

Question

DBO permissions on database replaced during RESTORE

praveen_vejandla

SSCrazy

Points: 2069
More actions
July 24, 2008 at 5:53 am

#122675

Suppose if I create a database on a development box as db_owner and then restore the backup of production version from another server onto this database, I am no longer able to access the database after RESTORE.
This is because permissions are stored within the database and they get replaced during RESTORE.
As I don't have db_owner permissions in production and even if I use sp_change_users_login, it won't fix the issue.
Is there any way db_owner permissions can be given after RESTORE again?
We would like to give the developers ability to restore the database but after restoration, what's happening is that they are forced to contact DBA to get db_owner permissions due to permissions getting replaced.
Is there any granular permission which can be given to developers at SERVER level such that they can run below command to get their db_owner privilege back without adding them to sysadmin role.
alter authorization on database:: to [login]
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 1

sp_change_user_login only fixes the orphaned users, as a restored db will have a diff SID from the logins.

In your scenario, you need to export the user,role and permission details(from the dev db) to another db or to a file before the restore on your dev db This can be done by executing sp_helpuser,sp_helprotect etc. Once the restore is over, you can import these users, and their respective permssions. You will then be able to access your db with the same permissions/roles as before.

praveen_vejandla SSCrazy Points: 2069 More actions · Answer 2

Thanks for the reply. Problem here is how can this be accomplished by developers itself without contacting DBA and we don't want to give sysadmin permissions to developers.

Deveopers has db_owner permission on database before RESTORE, they can generate the SQL code for permissions but after RESTORE, they lose the db_owner permission and they contact DBA for giving permissions again and this happens on a regular basis.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

I believe the server security_admin can be given to fix this.

Or set up a job that can run under system context and run sp_change_users_login to sync things up. The logins only have to be moved once to the new server and every new restore can use the system proc to sync up logins.

michaela SSCertifiable Points: 5649 More actions · Answer 4

Install and run dbo.sp_help_revlogin on the instance you are db_owner; copy the query result in a script for the logins you want to move then ask the prod dba to :

1)drop developers' logins in prod;

2)run the script you created in prod to re-create the logins in prod instance.

Now you can restore your db in prod and keep your initial permissions with no other intervention/fixes.

You can find sp_help_revlogin on one of ms web pages, just google it.

Good luck.

praveen_vejandla SSCrazy Points: 2069 More actions · Answer 5

Thanks for the replies.

security_admin server role is used to create logins and give access at the server but not at the database level. It didn't work.

sp_change_users_login will not work because developers don't have access on production database.

Giving permissions in production to developers is not an option as we want to restrict the access.

Thanks.

markinj Valued Member Points: 59 More actions · Answer 6

sorry to reply to old thread but have same scenario with one addition = To repeat the scenario ==> "Suppose if I create a database on a development box as db_owner and then restore the backup of production version from another server onto this database, I am no longer able to access the database after RESTORE. This is because permissions are stored within the database and they get replaced during RESTORE. As I don't have db_owner permissions in production and even if I use sp_change_users_login, it won't fix the issue. Is there any way db_owner permissions can be given after RESTORE again?

We would like to give the developers ability to restore the database but after restoration, what's happening is that they are forced to contact DBA to get db_owner permissions due to permissions getting replaced. Is there any granular permission which can be given to developers at SERVER level such that they can run below command to get their db_owner privilege back without adding them to sysadmin role. alter authorization on database:: to [login]" <==

Same situation; due to separation of duties developers have dbo on old box but DB is copied to higher environment and they will not be there, I have other users that are not on dev and cannot be on dev box so how can I add DBO to these users afe DB is restored via a job

Lowell SSC Guru Points: 323518 More actions · Answer 7

have your dba create a procedure that features EXECUTE AS OWNER that adds missing permissions.

have the procedure loop through each database on the dev server, and do a classic if not exists(select * from sys.database_principals)

create user....also add the users to db_owner or whatever roles are appropriate.

with the work scripted out once, he can grant developers execute to the procedure, so they can add themselves to newly restored databases. kind of wierd that they can restore a database, but not add themselves as users....

I've also fiddled with extended events, andlaunching a procedure which does the above when the extended event AUDIT_BACKUP_RESTORE_EVENT is kicked off.

i can't seem to find my code example yet.

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!

praveen_vejandla SSCrazy Points: 2069 More actions · Answer 8

You could develop a custom restore procedure that scripts out existing permissions into a table in database like MSDB and then at the end of restore, trigger a SQL agent job that reads the permission strings from table in MSDB database and executes them..

SQL agent job should be run under an account which has privileges to create users and apply permissions..