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!