June 24, 2008 at 9:15 am
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
July 29, 2008 at 4:44 am
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
July 30, 2008 at 7:10 am
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
July 8, 2009 at 1:15 pm
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?
July 9, 2009 at 9:26 am
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
July 15, 2015 at 2:25 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply