How can I restore a database such that I can use it on a server where I don't have the security admin role

Question

On a SQL 2008 instance, I have a login with the dbcreator and public roles. When I restore a database onto this server, I am then unable to use that database because all the user -> login mappings in it are broken (the ids are different on different servers).

There is an sp_change_users_login 'Auto_Fix', userName stored procedure, but I can't use this because I can't access the database in the first place. Lastly, since I don't have the securityAdmin role I can't map any logins to database users.

Is there a way to give myself permission to access a database as part of the restore process?

Answer 1

An authorized admin can write a helper procedure that fixes the mapping. It can then sign the procedure and give the necessary authorization to the procedure through code signing (see Signing an stored procedure). It can then grant you execute permission on this helper procedure and you will be able to execute the operation (fix the broken logins) w/o having the securityAdmin priviledge.

Answer 2

No, sorry.

"securityAdmin" is at the server level and is unrelated to any database rights

Note (FYI, not much use yet!) this is fixed for the SQL Server 2011 (aka Denali) with "contained databases"

Answer 3

As far as I know, you need someone with dbo to fix this for you. I could be wrong on this, maybe there is a backdoor way - but this is pretty much as designed. You only have dbcreator, so you can't muck around inside the DB once you've created it.

Answer 4

My eventual solution was to use a bulk import of every table in the database instead of a restore.