SQL 2005 database roles and security

Question

I Hope someone can clarify and help.
I've moved to creating/using schemas in Sql Server 2005, where I've also created database roles and assigned schemas to the roles.

The problem I have is one of the db roles I have should have full access - say SuperSysRole.
However, when I assign a login to that role, it cannot execute specific commands. In particular, I want a user within this role to manage password resets. However, when I execute the stored procedure, it does not return back all users in the database.

The stored procedure has been which returns back the details calls a view which integrates the sysusers and sysmembers tables.

The user, say myAdmin, has been assigned to the role SuperSysRole. The role SuperSysRole has been granted execute to the stored procedure selectRoles.

It's probably a simple thing but can someone please tell me what I am missing?
My application authenticates using the myAdmin user name and password. It works fine when I login with the sa account.

Answer 1

Database role membership is queryable through the sys.database_principals and sys.database_role_members catalog views, which are the proper catalog views to use instead of the deprecated sysusers and sysmembers. Both have the same security restrictions:

In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission.

The necessary permissions are listed in GRANT Database Principal Permissions: VIEW DEFINITION. There is no permission that can be granted to view any principal and role definition.

Your best option is to create a procedure that selects from the proper metadata catalog views and sign this procedure with a credential that is added into the db_securityadmin role. See Signing an Activated Procedure for an example how to sign a procedure. The grant EXECUTE on this procedure to whoever required to view these principals and role memberships.

However, all of this will get you nowhere with regard with the declared intent: manage password resets. Only server principals (aka. logins) have passwords, and any scheme designed at the database principals level can have no effect on server principals, since they are the wrong abstraction. And since there are no server roles to define (you can only have Windows groups as roles, but you want to use SQL Auth), you cannot have this done by role membership.

My recommendation is, again, to use code signing. Create procedures that do the activities you want (query sys.server_principals and sys.sql_logins to inspect password expiration, issue ALTER LOGIN statements to reset passwords etc) then use code signing to sign these procedures and grant the required privileges to the signature. I refer you again to Signing an activated procedure for an example how to do that. Then grant EXECUTE on these procedures to the 'special' users that have to manage these.