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

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.