0

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?

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.

  • While this isn't the solution I went with, it does work. – GlennS Jul 11 '11 at 12:17
1

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"

0

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.

  • 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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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