Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
While this isn't the solution I went with, it does work. –  GlennS Jul 11 '11 at 12:17
add comment

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"

share|improve this answer
add comment

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.

share|improve this answer
1  
This is not quite true: if you create a database then you get a user created for you in it, and so can access it. If you restore one then this doesn't happen. –  GlennS Jul 11 '11 at 12:16
    
Thanks for the clarification. –  mfinni Jul 11 '11 at 12:33
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.