May 12, 2010 at 12:13 pm
I am confused on why this won't work. On a SQL Server 2005 9.00.4035, I create an NT group login TEST_DB_SECADMIN and granted securityadmin permission to it. This allowed me to add login AATESTJOE to the server but not any databases.
SELECT * FROM fn_my_permissions (NULL, 'SERVER');
entity_namesubentity_namepermission_name
server CONNECT SQL
server ALTER ANY LOGIN
server VIEW ANY DATABASE
server VIEW ANY DEFINITION
server VIEW SERVER STATE
I then added TEST_DB_SECADMIN to db_securityadmin and db_accessadmin roles in database MAINT.
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
entity_namesubentity_namepermission_name
database CREATE SCHEMA
database CREATE ROLE
database CONNECT
database ALTER ANY USER
database ALTER ANY ROLE
database ALTER ANY APPLICATION ROLE
database VIEW DATABASE STATE
database VIEW DEFINITION
When logged on as TEST_DB_SECADMIN, I try to grant AATESTJOE db_datareader in MAINT.
exec sp_addrolemember N'db_datareader', N'AATESTJOE'
Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 51
User does not have permission to perform this action.
What am I missing?
May 13, 2010 at 4:11 am
BP-503183 (5/12/2010)
I create an NT group login TEST_DB_SECADMIN and granted securityadmin permission to it.
you need to set db_owner
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 13, 2010 at 6:55 am
Bhuvnesh (5/13/2010)
BP-503183 (5/12/2010)
I create an NT group login TEST_DB_SECADMIN and granted securityadmin permission to it.you need to set db_owner
If I set db_owner then I don't need to set db_securityadmin or db_accessadmin. But this will give SECADMINTEST full database rights, including data access. That isn't what I am trying to do.
I am trying to setup a user that only has permissions to add or drop database access, assign or drop permissions to objects. According to BOL that is what db_accessadmin and db_securityadmin should allow.
May 14, 2010 at 6:58 am
I think I found the answer to what I am doing wrong.
BOL has definition of db_securityadmin: Members of the db_securityadmin fixed database role can modify role membership and manage permissions.
However to add a user to database role, the stored procedure sp_addrolemember is used.
sp_addrolemember permissions:
Adding members to flexible database roles requires one of the following:
Membership in the db_securityadmin fixed database role.
Membership in the role that owns the role.
ALTER permission on the role.
Adding members to fixed database roles requires membership in the db_owner fixed database role.
I don't know why db_securityadmin could have the statement only for flexible database roles.
May 14, 2010 at 7:34 am
BP-503183 (5/14/2010)
Adding members to fixed database roles requires membership in the db_owner fixed database role.
Did i suggest you right above ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply