Geek Answers Handbook

How to create a Resource Governor classifier function based on a database role?

I am trying to write a classifier function for a SQL 2008 resource regulator. I would like to use a user-created database role to determine if a user should be a member of a particular workload group. Incoming logins are SQL logins. I cannot use IS_MEMBER () because IS_MEMBER is limited by the context of the current database (in this case, master). I cannot use [user database] .sys.database_principals because the classifier function must be bound to a schema (thus limiting the search in the context of the current database). In addition, any views referenced by the function must also be associated with a schema, which means that I cannot create a view in master to refer to user database security views.

The goal is, in principle, to execute IS_MEMBER () from the wizard to check the role in another database.

+5
source share
2 answers

You can create a DDL trigger in your database, which updates the table in the main one, so that you also have all the information about the user / group. Then you can request this. You probably want to attach a trigger to ADD_ROLE_MEMBER and DROP_ROLE_MEMBER.

I'm just starting to work with Resource Governor, so if I come across a “cleaner” way to do this, I will post it again.

+3
source

MSDN says:

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

, , IS_MEMBER.

, , , , . , 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

, , :)

0

More articles:


All Articles