Post reply

dbo to add rolemember???

  • November 19, 2003 at 7:50 am

    #84743

    Do you have to be a sys admin or dbo in the database to add users to a role?

    Here's scenario:

    I have a security team that wants to take over adding users to dB. They are asking for securityadmin serverrole access and accessadmin dB access. I have agreed to grant them this but when they tried to add a user to a role (datareader)SQL server gives error message of "User does not have permission to add user to role"

    Any ideas on how to get around this. I do not want to grant sysadmin rights, just so they can add users to roles. They have no problem adding users or logins.

    TIA,

    aurora01


    Aurora

  • November 19, 2003 at 10:10 am

    #482698

    This sounded ok to me so I tried it and got the same error you did,

    I then created my own role and found I could add members to my role.

    eg: sp_addrole 'Student'

    GO

    sp_addrolemember 'Student', 'test2'

    GO

    sp_addrolemember 'db_datawriter', 'test2'

    GO

    gave me this result:

    'test2' added to role 'Student'.

    Server: Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 49

    User does not have permission to perform this action.

    So I check in BOL under sp_addrolemember. "Only members of the sysadmin fixed server role and the db_owner fixed database role can execute sp_addrolemember to add a member to fixed database roles. Role owners can execute sp_addrolemember to add a member to any SQL Server role they own. Members of the db_securityadmin fixed database role can add users to any user-defined role."

    SO you can work with user defined roles but if you want to use SQL Server db roles you must be db_owner.

    Francis

  • November 19, 2003 at 11:54 am

    #482722

    You just need to be dbo to add user to a role.

    mom

  • November 19, 2003 at 9:21 pm

    #482780

    A alternate work around would be to add the security group's ID to the db_securityadmin database role. Create a new user-defined role and add that role to the db_datareader fixed role. Since the security group is a member of the db_securityadmin group they can add users to user-defined roles. This is a bit of a kludge since you will have to create a user-defined role to mimic all the fixed roles and do this in every database.

  • November 20, 2003 at 6:23 am

    #482840

    You dont have to give them sysadmin rights or DB owner to add new users to roles. If you give them securityadmin server role and then in the database give them securityadmin, accessadmin and what ever other DB roles you want them to be able to assign. In this case it would be datareader.

    They dont have to be DB_owner.

  • November 20, 2003 at 7:21 am

    #482854

    mforbes,

    The problem is "you do" have to be a dbo or sysadmin to add users to roles. They "do not" have to be sysadmins or dbo's to add logins or users, just members of the security admin server role and acccessadmin dB role.

    I think Randy may be on to something. It is true this will be tedious as the dB server I am referring has 14 dB's on it. Any other thoughts?

    TIA,

    aurora01


    Aurora

  • November 20, 2003 at 7:31 am

    #482857

    Randy's idea is interesting. Although ultimately I don't think the db fixed roles are very useful within the context of an application. They are fine for administration but application users may be better suited to using user defined roles with access via stored procs or specific views. Randy's workaround may be a bit of a kludge but then so is db_datareader and db_datawriter, which may be why SQL Server only lets sysadmins and db_owners assign users to fixed db roles.

    Francis

  • November 20, 2003 at 7:38 am

    #482859

    Agreed regarding the app! But in this situation we just want to give the security team permission to add/remove logins, and then add/remove users to the datareader role.


    Aurora

  • November 20, 2003 at 9:38 am

    #482914

    I don't think the security team need to add or remove login. User could be removed from any database by database owner or security admin withing that database, but to have login remove or added by anybody but sa is kind of questionable.

    mom

  • November 21, 2003 at 10:44 am

    #483166

    Just a thought if your company wrote/controls the application source code. This functionality could be added to a screen so that authorized people could add users (which adds them to the required role) Assuming the administrator is authorized the connection to the database would then be made as an authorized id. This also means now its the developer who gets to implement this and not the DBA

    Francis

  • November 21, 2003 at 10:46 am

    #483167

    fhanlon,

    I tend to agree with that option and feel a bit more comfortable with that.

    Thanks all for your input, you all are awesome!

    Edited by - aurora01 on 11/21/2003 10:47:37 AM

    Edited by - aurora01 on 11/21/2003 2:10:16 PM


    Aurora

  • November 21, 2003 at 2:09 pm

    #483230

    Are you having to use SQL Server logins? If not, use Windows groups. The security can then manage permissions by managing the Windows group. Saves a bunch of cleanup for when a person leaves and reduces the work when a person needs to be added.

    K. Brian Kelley, GSEC

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

Viewing 12 posts - 1 through 11 (of 11 total)

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