dbCreator can restore a database but not then access it

  • Jimmy M

    Default port

    Points: 1481

    Dear all,

    I have a consultant coming in when I am out of the office. He is doing an application installation, including a database creation component. I had the idea that I would give him a sql server login that is in the dbCreator fixed server role so that he could create the database and implicitly therefore would own the database.

    This works well if he is creating a new database using CREATE DATABASE. However, i have learned that he needs to create the database by restoring from backup files he will bring with him. When a sql server login that is in the dbCreator fixed server role (but not in the sysadmin fixed server role) restores a database, the login is marked as the database owner. However, the login cannot access the database. A "USE " fails with

    Msg 916, Level 14, State 1, Line 1

    The server principal " under the current security context.

    I assume the problem is in the record for dbo in the sys.database_principals table, which still has a sid for the previous database.

    Does anybody know how i might get around this problem, or know an alternative security model that let's a sql server login create a database using RESTORE DATABASE and can actually access the database afterwards!

    Many thanks.

    James

  • Jimmy M

    Default port

    Points: 1481

    If anyone is interested, I didn't get around this problem. I temporarily gave the consultant SA privileges!

    An update of the user system table to marry up the SIDs following the restore probably would have done the trick, however, but that's far from a clean solution.

    Cheers.

    James

  • 2 Tim 3:16

    SSCarpal Tunnel

    Points: 4431

    Possibly an orphaned sid problem. You may need to run the sp_change_users_login procedure to fix them after your retore.

    syntax: sp_change_users_login 'auto_fix','userid'

    to identify if you have an orphaned sid;

    sp_change_users_login 'report'

    Tim White

  • Ryan D.

    SSCommitted

    Points: 1535

    Just question, since I find myself in a similiar situation:

    User A wants to be able to restore from prod to dev. If the resync is required what permissions are needed to allow user A to resync the account?

  • Tom Schuettke

    SSCarpal Tunnel

    Points: 4103

    Ryan:

    Since you are altering security on the database it requires higher permission levels than most users.

    From Books Online:

    "Requires membership in the db_owner fixed database role. Only members of the sysadmin fixed server role can specify the Auto_Fix option."

    Be sure to read the full description and options in Books Online before using sp_change_users_login.

    Tom

  • blurrytextfile

    SSC Enthusiast

    Points: 179

    I am having the same issue. A proxy account has been added to the dbcreator role for the purpose of restoring a database via SSIS. However, that proxy cannot create database principals because it does not have access in the database it just restored. I don't want to grant the proxy more access than is absolutely necessary.

Viewing 6 posts - 1 through 6 (of 6 total)

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