December 3, 2003 at 1:56 pm
that in order to add a member to a role one must be a dbo or sysadmin.
I have posted this before and I have tried a variety of options. There must be a way to grant my security admin enough permission to add a member to the datareader role without granting them sysadmin rights. If anyone has found a way to get around this please let me know how you did it?
aurora01
Aurora
December 3, 2003 at 2:18 pm
Based on the sp_addrolemember stored procedure, only members of the db_owner role can add a user/role to a fixed db role. Here is the relevant code:
-- CHECK PERMISSIONS --
-- Only member of db_owner can add members to db-fixed roles --
if (not is_member('db_owner') = 1) and
(not (@roluid < 16400 and is_member('db_owner') = 1)) and
(not (@roluid >= 16400 and is_member('db_securityadmin') = 1)) and
(not (@roluid >= 16400 and is_member(user_name(@owner)) = 1))
begin
dbcc auditevent (110, 1, 0, NULL, @membername, @rolename, NULL)
raiserror(15247,-1,-1)
return (1)
end
else
begin
dbcc auditevent (110, 1, 1, NULL, @membername, @rolename, NULL)
end
The work around is to create a user-defined database role. Add that role to db_datareader. Then securityadmins can add users to the user-defined database role. For instance:
USE MyDB
GO
EXEC sp_addrole 'Readers'
EXEC sp_addrolemember 'db_datareader', 'Readers'
GO
A security admin would later come in and add a user to the Readers role:
EXEC sp_addrolemember 'Readers', 'MyUserInDB'
GO
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 3, 2003 at 2:24 pm
Ahhhhh!!!
You are the best!
Thanks!
Aurora
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply