locked
Administering Logins RRS feed

  • Question

  • Hi,

    I am trying to setup a someone to be able to administer logins to sql and the DB’s.     I need this person to ONLY be able to add/mod/del logins at the sql instance level and at the DB level.  They would handle all login/user requests. 

     

    I gave this person SECURITYADMIN server role  and DB_ SECURITYADMIN in a database, but it throws up errors when they try to add a login to the database. 

    The errors said something about:  "User does not have permission to perform this action.  Procedure sp_grantdbaccess/sp_addrolemember"


    The login does get added at the instance level.

    any ideas why the error msgs?   BooksOn,line make it look like SECURITYADMIN & DB_ SECURITYADMIN is sufficient.

    thanks,

    bill

     

    Friday, December 4, 2009 1:38 PM

Answers

  • BOL clearly states that role db_securityadmin can be used for adding users in to flexible database roles and not the FIXED database roles.
    You have to be a member of DB_OWNER if you are going to add any users in to one of the fixed database role.
    Have a look at the BOL on sp_addrolemember - http://msdn.microsoft.com/en-us/library/ms187750.aspx
    where it says - Adding members to fixed database roles requires membership in the db_owner fixed database role.

    Thanks, Leks
    Friday, December 4, 2009 6:40 PM
  • There are two types of database-level roles in SQL Server: 

    fixed database roles:  predefined in the database

    flexible database roles: that you can create.

     

    db_securityadmin database roles can manage fixed database role membership and can add the user to flexible database role.

    However, only members of the db_owner database role can add members to the fixed database role. 

    My suggestion add login id to db_owner database role.


    Rajeev R
    Saturday, December 5, 2009 5:25 PM
  • Yes. DB_owner role is required if that user has to allow access to other users to database.
    Thanks, Leks
    Monday, December 7, 2009 2:55 PM

All replies

  • BOL clearly states that role db_securityadmin can be used for adding users in to flexible database roles and not the FIXED database roles.
    You have to be a member of DB_OWNER if you are going to add any users in to one of the fixed database role.
    Have a look at the BOL on sp_addrolemember - http://msdn.microsoft.com/en-us/library/ms187750.aspx
    where it says - Adding members to fixed database roles requires membership in the db_owner fixed database role.

    Thanks, Leks
    Friday, December 4, 2009 6:40 PM
  • There are two types of database-level roles in SQL Server: 

    fixed database roles:  predefined in the database

    flexible database roles: that you can create.

     

    db_securityadmin database roles can manage fixed database role membership and can add the user to flexible database role.

    However, only members of the db_owner database role can add members to the fixed database role. 

    My suggestion add login id to db_owner database role.


    Rajeev R
    Saturday, December 5, 2009 5:25 PM
  • I saw that about db_owner, but i took it to mean that db_owner can ALSO do the "add member" thing.   my goal was to have someone ONLY do the logon administering and have no other "rights".  now it looks like they need to be DBO in each database also.   am i understanding this correctly?
    Monday, December 7, 2009 1:14 PM
  • thanks.  so, there is no way to have someone only have rights to do logins to instance & database, but have not other rights?
    Monday, December 7, 2009 2:13 PM
  • Yes. DB_owner role is required if that user has to allow access to other users to database.
    Thanks, Leks
    Monday, December 7, 2009 2:55 PM
  • thanks for getting back to me so quickly.   that seems to defeat the prupose of having a db role of DB_SECURITYADMIN.   i wonder if there's a way to programatically give DBO behind the scenes, then revoke it.

    much thanks,
    bill

    Monday, December 7, 2009 4:11 PM