Who cand add users to the db_securityadmin role ?

This is by design. According to the SQL Server 2000 documentation for sp_addrolemember (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_addp_4boy.asp), only members of db_owner fixed role (or higher privileged users) can add members to fixed database roles:

“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.”

I run a simple test onSQL Server 2000 SP4 to verify if this is indeed the case or a BOL bug:

-- Running on SQL Server 200 SP4

1> exec sp_addrolemember 'db_securityadmin', 'user_2'

2> go

Msg 15247, Level 16, State 1, Server RAULGA-W2K3\SHILOH, Procedure sp_addrolemember, Line 49

User does not have permission to perform this action.

Here is a possible workaround for SQL Server 2005. Notice that as it is not possible to modify the set of privileges for a fixed role, you will need to grant the execute permission directly to the users, or use a user defined role for this purpose.

-- User_1 is a member of db_securityadmin

--

exec sp_addrolemember 'db_securityadmin', 'user_1'

go

-- Create a stored procedure that wil run under dbo's context

-- this SP will call sp_addrolemember with the parameters specified,

-- but as it will run under dbo's context, it will be able to add

-- members to DB fixed roles

--

CREATE PROC sp_addrolemember_elevated( @role_name sysname, @user_name sysname )

WITH EXECUTE AS 'dbo'

AS

exec sp_addrolemember @role_name, @user_name

go

-- Create a user-defined role for granting access to sp_addrolemember_elevated

--

CREATE ROLE role_addrolemember_elevated

go

-- Grant permission to execute sp_addrolemember_elevated to members of role_addrolemember_elevated

--

GRANT EXECUTE ON sp_addrolemember_elevated TO role_addrolemember_elevated

go

-- We need to explicitly add user_1 as a member of the newly created UDR

EXEC sp_addrolemember role_addrolemember_elevated, user_1

go

-- Now, let's try the new SP under the

--

EXECUTE AS LOGIN = 'user_1'

go

-- Should fail!

exec sp_addrolemember 'db_securityadmin', 'user_2'

go

-- Should succeed!

exec sp_addrolemember_elevated 'db_securityadmin', 'user_2'

go

REVERT

go

-- We can verify that user_2 is now a member of db_securityadmin

--

SELECT user_name(role_principal_id) as role_name,

user_name(member_principal_id) as member_name

FROM sys.database_role_members

go

If you have observed that a member of db_securityadmin can bypass this restriction and add another principal to a fixed role (on SQL Server 2000 or SQL Server 2005), it is probably a bug and I would really appreciate if you can contact us to investigate the scenario.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

RaulGarcia-MS at 2007-8-30 >

By the way, I forgot to mention that the workaround I posted would potentially allow any principal who has been granted permission to execute it to add any principal (including herself) as a member of any fixed role (including db_owner).If you require this kind of flexibility and you trust the principals who are members of the role_addrolemember_elevated role, it may be OK, but I would also recommend monitoring for undesired role memberships regularly and other unauthorized actions; in other words: trust but verify.

SELECT user_name(role_principal_id) as role_name,

user_name(member_principal_id) as member_name

FROM sys.database_role_members

go

A better option would be to modify the stored procedure to limit the fixed roles they can modify, for example:

-- Create a stored procedure that wil run under dbo's context

-- This SP will call sp_addrolemember with the user name specified

-- and add her to 'db_securityadmin' fixed role.

--

CREATE PROC sp_addrolemember_db_securityadmin( @user_name sysname )

WITH EXECUTE AS 'dbo'

AS

exec sp_addrolemember 'db_securityadmin', @user_name

go

This would minimize the potential for abusing the elevated privileges of the stored procedure, but in any case, it is always a good idea to monitor for undesired activity.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

RaulGarcia-MS at 2007-8-30 >