Post reply

DBO permissions to a user

  • June 21, 2012 at 6:24 am

    #263124

    How can i assign dbo/db_owner permissions to a user ?

  • June 21, 2012 at 6:27 am

    #1503893

    Look sp_addrolemember for script way of doing it

    Or just use SSMS to alter the user in the security folder and grant db_owner on the user in the database,

    But are you sure you want to give them DBO rights?

  • June 21, 2012 at 6:46 am

    #1503898

    Will this script do:

    sp_addrolemember 'db_owner', 'user_name'

    I want to grant my database developers dbo permissions on development environment.

  • June 21, 2012 at 6:47 am

    #1503899

    Yes looks fine to me.

  • June 21, 2012 at 7:08 am

    #1503908

    Adding Database Users to the db_owner Role will work fine however this is a good time to start thinking about User-defined Database Roles. I like to only grant permissions, including via Fixed Database Role Membership, to a User-defined Database Role. Given that we are talking about db_owner Role (not the same as dbo) it may seem redundnant, but even in this case it is useful. Consider a case when you want to temporarily, or even permanently, downgrade what your developers can do in the database. You can simply remove your User-defined Role from the db_owner Role without affecting who belongs to your logical "developer" grouping, then easily add it back, or change their permissions as a group.

    Might I recommend creating a new User-defined Database Role in your database called Developers, adding all your developers into the Developers Role, and then adding the Developers Role to the db_owner Fixed Role. Like so:

    USE [YourDatabaseHere]

    GO

    -- create new role for your developers to belong to

    CREATE ROLE Developers

    GO

    -- add Developers Role to db_owner

    EXEC sys.sp_addrolemember

    @rolename = N'db_owner',

    @membername = N'Developers';

    GO

    -- add your developers to your Developers role

    EXEC sys.sp_addrolemember

    @rolename = N'Developers',

    @membername = N'Sally';

    EXEC sys.sp_addrolemember

    @rolename = N'Developers',

    @membername = N'Jim';

    -- etc.

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 25, 2012 at 2:12 am

    #1504912

    Hello opc.three. I agree with you. Even follow the same rules for providing DB permissions. This was required for temporary basis so it worked fine:

    sp_addrolemember 'db_owner', 'user_name'

    Thanks to all for helping me out.

  • July 23, 2012 at 5:59 am

    #1516168

    Just seen this post but need to clarify.

    There is a difference between the user\schema dbo and the database role Db_Owner.

    A user who is the owner of a database will map into the db directly as the user dbo and will automatically inherit DB_owner permissions. There will be no database user for that particular login.

    For example

    Create login Myapp_user and set the login as the database owner.

    The login now maps to the db via dbo, there will be no database user Myapp_user, the login Myaap_user will have have full owner permissions on the database

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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