Few days ago I worked for one client. He uses the following business model:
- dbo usually responsible for high level database design and maintenance;
- all database users organized in additional security groups for security purposes;
- dedicated person is responsible for user’s security maintenance, (s)he is able to assign user to explicit (one or more) application security group. This dedicated person is a department’s secretary, (s)he is trusted person, has only right to run simple SELECT statement on some tables, but (s)he is member of db_securityadmin database role.
Firstly it seems to me that security rules are appropriate and nothing can happen. Secretary is unable to maintain built-in database roles like db_datawriter etc.
Please login under account – member of db_securityadmin to check
SELECT * FROM fn_my_permissions(N'db_name', N'DATABASE')
you will get the following list of privileges (according to BOL).
-- ALTER ANY APPLICATION ROLE
-- ALTER ANY ROLE
-- CREATE SCHEMA
-- VIEW DEFINITION
But if you run
GRANT CONTROL TO <secretary> -- grant database control to self
and try to run
SELECT * FROM fn_my_permissions(N'db_name', N'DATABASE')
after that to ensure <secretary> now become dbo!
Please be careful to use odd database security groups. Also only trusted person should be assigned to this group. As for now (SQL Server 2005 member of db_securityadmin) equal to database owner.
Next article I will show you potential problems in more details.
