allow restore but not read/write

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

SQL Server Administration (2005)

allow restore but not read/write

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2009-05-21 : 18:35:19

I'm trying to figure out if there's a way to give a login whatever rights it needs to restore a database (e.g. DBCREATOR) and give the login the rights to map a different login to a user in the database after it was restored, without making it a sysadmin. I don't want the login to be a sysadmin because there will be certain databases it can't have rights to. Here's the script I was trying to use, and the result I was getting.

RESTORE DATABASE mydb FROM DISK = '\\myserver\baselines\mydb.bak' WITH REPLACE

USE [mydb]

IF EXISTS (SELECT * FROM sysusers WHERE [name] = 'mydb_user')
BEGIN 
 DROP USER mydb_user
END

CREATE USER mydb_user FOR LOGIN mydb_user

EXEC sp_addrolemember 'db_owner','mydb_user'

Result:

Msg 916, Level 14, State 1, Line 1
The server principal "test_login_dbcreator" is not able to access the database "mydb" under the current security context.

The only way I can think of to get around this is to use separate instances of SQL Server. Am I missing something?

nwalter
Starting Member

39 Posts

Posted - 2009-05-21 : 23:27:02

You could make a stored procedure in mydb using execute as to do the login changes to the DB and then just grant that user execute permission to the stored procedure. You could also place the stored procedure in the master DB, or another DB called admin or something so that you're not depending on that procedure being in the database that was restored.

Of course this person still isn't going to have any access to the database they restored, if you need them to have access to the database after it's resored their login would need to be a DBO in the database before it was backed up or you could do the same procedure as above except change it to grant them DBO to the database.

Why do you want a non-sysadmin to do a database restore anyway?

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2009-05-22 : 12:37:40

That sounds like more trouble than it's worth, but I appreciate the suggestion. I have developers who do deployments for QA using nAnt scripts (including the db push), and the developers can't all be sysadmins on the database servers because we have customer data that not all devs are allowed to see.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources