db_securityadmin Limitation

Recently I was working on a security audit where i was required to give a SQL Login permissions to run the following Query,

 

CREATE LOGIN Vinoth WITH PASSWORD='', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE USER [Vinoth] FOR LOGIN [Vinoth]

sp_addrolemember 'db_datareader', Vinoth

sp_addrolemember 'db_datawriter', Vinoth

CREATE SCHEMA Vinoth AUTHORIZATION Vinoth

ALTER USER [Vinoth ] WITH DEFAULT_SCHEMA=[Vinoth]

 

The SQL Login was initially assigned Sysadmin by the third party which we obviously did not want to grant. So we gave the user

the following rights to run the task,

1. Security Admin(Server Role)
2. db_accessadmin
3. db_securityadmin
4. CREATE SCHEMA Rights
5. ALTER USER Rights.

After which he was able to execute all the task successfully except for adding rolemember part,

 

sp_addrolemember 'db_datareader', Vinoth

sp_addrolemember 'db_datawriter', Vinoth

 

Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 51
User does not have permission to perform this action.

Even though the user had db_securityadmin rights he was not able to run the following query. On further research I found the permissions required for sp_addrolemember  as below from MSDN,

 

"Only members of the sysadmin fixed server role and the db_owner fixed database role can execute sp_addrolememberto 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."

 

Eventually no fixed db roles can be added so we ended up automating the process with a Store Proc EXECUTE AS clause to avoid giving user elevated rights.