Post reply

Setup ADMIN Access to ONLY One Database

  • January 7, 2010 at 1:58 pm

    #214332

    How do I configure one User with ADMIN (sa) Access over their own database?

    There are multliple databases on this server and I want to ensure that they can only access their own database.

  • January 7, 2010 at 2:03 pm

    #1100499

    To make them an ADMIN of their own database, can I assign them to the 'DB_OWNER" Database-Level Role?

  • January 7, 2010 at 5:09 pm

    #1100611

    The db_owner role is probably a good choice, but be aware that a user in this role can drop the database. You definitely don't want to allow the user to use SA or make him a member of sysadmin.

    You don't mention exactly what you want to allow the user to do and depending on that, you may be able to get away with a combination of other fixed database roles e.g. db_datareader, db_ddladmin, db_backupoperator, etc.

    Greg

  • January 7, 2010 at 5:13 pm

    #1100613

    Thanks, Greg. They require "sa" privileges on the Database. So db_owner seems to be what I need. That way they can do whatever they want within that database, and not have access to the other databases on the Server. And you are correct, I do NOT want to give them the "sa" password or assign them to the sysadmin group.

    Thanks again!

  • January 8, 2010 at 2:15 am

    #1100748

    you might want to implement auditing on your SQLServer, especially if you granted a user to the db_owner. Having db_owner has all the privileges on the database including dropping database and granting access to the database.

  • January 8, 2010 at 9:47 am

    #1101007

    Thanks, Jocelyn!

    Good tip. This is being done ONLY in Development, but, it's still a good idea.

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

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