locked
SQL Server security question RRS feed

  • Question

  • Hey there,

    I gave a user the server privilege dbcreator, and the database role membership dbowner to DATABASE1.

    2 issues I am having:

    1. When he creates a database, he only has public access to it himself. He can create the database successfully, but shouldn't he be the owner as well? Can he give himself access to be the owner?

    2. When restoring a database, which he can succesfully do, he loses all access to that database.

    eg: DATABASE1 is there, and he is the dbowner. He then restores DATABASE1 from production successfully to the current server, and now he cannot access that same database at all and sure enough when I look at his access he doesn't even exist anymore for that database.

    Can someone explain this to me, thanks in advance!

    Wednesday, May 16, 2012 9:29 PM

Answers

  • There are two different security points that I think you are mixing together here.  There is a database owner, which is the account that actually owns the database and then there is the db_owner role.  The database owner is set in the database level properties and isn't a security aspect.  If you create a database you are the database owner by default.  What you are describing is that you gave someone permissions to the db_owner role inside of the database but they aren't the database owner.  Database level permissions like the db_owner role are mapped to a Login SID that has a database User inside of the database.  If a database is restored all of the permissions revert to the permissions stored inside of the database when it was backed up, so if there is a Login SID mismatch with the database User, or there wasn't a database User for the Login for this person, they would lose their permissions.  You would have to goto the source system and provide them with the db_owner role there so that it is carried over as a part of the database backup, or create a stored procedure/Agent Job that can be executed by a lower privilege user, that elevates access inside of the execution only to add the database User and Role assignments back to the Login once the restore completes.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by gmazza88 Thursday, May 17, 2012 3:13 PM
    Wednesday, May 16, 2012 10:11 PM

All replies

  • There are two different security points that I think you are mixing together here.  There is a database owner, which is the account that actually owns the database and then there is the db_owner role.  The database owner is set in the database level properties and isn't a security aspect.  If you create a database you are the database owner by default.  What you are describing is that you gave someone permissions to the db_owner role inside of the database but they aren't the database owner.  Database level permissions like the db_owner role are mapped to a Login SID that has a database User inside of the database.  If a database is restored all of the permissions revert to the permissions stored inside of the database when it was backed up, so if there is a Login SID mismatch with the database User, or there wasn't a database User for the Login for this person, they would lose their permissions.  You would have to goto the source system and provide them with the db_owner role there so that it is carried over as a part of the database backup, or create a stored procedure/Agent Job that can be executed by a lower privilege user, that elevates access inside of the execution only to add the database User and Role assignments back to the Login once the restore completes.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by gmazza88 Thursday, May 17, 2012 3:13 PM
    Wednesday, May 16, 2012 10:11 PM
  • Makes great sense Jonathan, I thank you for your time!
    Thursday, May 17, 2012 3:14 PM