How do I build a Resource Governor classifier function based off of a database role?

Question

I am trying to write a classifier function for the SQL 2008 resource governor. I would like to use a user created database role to identify if the user should go into a particular work load group. The logins in question are SQL logins. I can not use IS_MEMBER(), because IS_MEMBER restricts itself to the current database context (in this case, master). I can't use [user database].sys.database_principals because the classifier function must be schema bound (thus restricting lookups to the current database context). Furthermore, any views referenced by the function must also be schema bound, meaning that I can't create a view in master to reference the user database's security views.

The goal here is to be able to basically execute IS_MEMBER() from master to check a role in another database.

Answer 1

MSDN says:

The following system functions can be used for classification: HOST_NAME(), 
APP_NAME(), SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER(), and IS_MEMBER().

That would suggest the governor function is executed in the default database of the end user, and you can use IS_MEMBER there.

If not, I guess you can join together some system views to check if the current user is in a role. System views allow you to specify a three part name, like TestDb.sys.database_principals:

select  *
from    master.sys.login_token l
join    TestDB.sys.database_principals m
on      l.sid = m.sid
join    TestDB.sys.database_role_members rm
on      rm.member_principal_id = m.principal_id
join    TestDB.sys.database_principals r
on      rm.role_principal_id = r.principal_id
where   r.name = 'testrole' -- Role Name
        and l.name = SUSER_NAME() -- User Name

This sounds rather complex and insecure, so I hope there is a better answer :)

Answer 2

You can create a DDL trigger in your database that updates a table in master so that you have all of the user/group information there as well. You can then query against that. You would probably want to attach the trigger to ADD_ROLE_MEMBER and DROP_ROLE_MEMBER at the very least.

I'm just starting to work with the Resource Governor, so if I run across a "cleaner" way of doing it, I'll post here again.