2

I have added a new windows account [PROD\apdf] and given it the dbcreator role.

  1. The user initiates a restore from an app server.
  2. The database restores correctly. Under database properties it shows the prod\apdf user as the database owner. However, that user is not granted access to the database. The sysadmins and dbcreators are added to security on the new database, but not the one it was restored by. When pulling up the dbcreator role list, the [PROD\apdf] user is on that list.

I have made sure that the account is not orphaned and it shows it is a member of the dbcreator role using:

EXEC sp_helpsrvrolemember 'dbcreator'; 

I have seen an article that indicates this may be by design, but I wanted to see if anyone else had an answer about this.

1 Answer 1

1

After the database is restored, a member of the sysadmin or securityadmin server roles will need to alter the authorization (ownership) of the database to the login:

ALTER AUTHORIZATION ON DATABASE::<name of database> TO [PROD\apdf];

I can verify the restore operation does not seem to correctly grant ownership through the below MCVE.

  1. create a login and make it a member of the dbcreator server role:

    CREATE LOGIN backup_restore_test
    WITH PASSWORD = 'asdfousd^f89aw09e8r90qawer$udfjoia'
        , CHECK_POLICY = OFF
        , CHECK_EXPIRATION = OFF;
    
    ALTER SERVER ROLE dbcreator ADD MEMBER backup_restore_test;
    
  2. Take a backup of the model database, to restore later:

    BACKUP DATABASE model 
    TO DISK = 'C:\temp\model.bak'
    WITH COPY_ONLY;
    
  3. Login to the server using the [backup_restore_test] user, and run the following:

    RESTORE DATABASE newmodel
    FROM DISK = 'C:\temp\model.bak'
    WITH MOVE 'modeldev' TO 'C:\temp\modeldev.mdf'
        , MOVE 'modellog' TO 'C:\temp\modellog.ldf'
        , RECOVERY;
    

    This shows the backup_restore_test login is the owner of the database:

    SELECT d.name
        , sp.name
    FROM sys.databases d
        INNER JOIN sys.server_principals sp ON d.owner_sid = sp.sid
    WHERE d.name = 'newmodel';
    

    However, this fails:

    SELECT *
    FROM newmodel.sys.tables
    

    The error:

    Msg 916, Level 14, State 1, Line 14
    The server principal "backup_restore_test" is not able to access the database "newmodel" under the current security context.

  4. Switch back to a sysadmin login, and run this:

    ALTER AUTHORIZATION ON DATABASE::[newmodel] TO [backup_restore_test];
    

    Now, this works, when logged in as the [backup_restore_test] login:

    SELECT *
    FROM newmodel.sys.tables
    

Unless I'm overlooking something, the backup_restore_test login should have access to the database as soon as it is restored since it owns the database at that point. I've verified this behavior on SQL Server 2012, and SQL Server vNext

5
  • 1
    I guess the question is, since you duplicated it as well, does that seem to be the expected behavior that a dbcreator role does not have access to the database they restored? Mar 7, 2017 at 19:51
  • do you still have the link to the article that says this is by design? I can't find anything.
    – Hannah Vernon
    Mar 7, 2017 at 19:59
  • 1
  • I've upvoted that connect item, and provided a link back here so Microsoft can see the inconsistency.
    – Hannah Vernon
    Mar 7, 2017 at 20:24
  • I appreciate the confirmation on this. I can understand both sides of the logic about giving and not giving access. We'll have to use a different security model to allow restores and be able to modify them. Mar 7, 2017 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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