Challenge in writing a classifier function. Please assist.
We have a challenge of creating the classifier function for resource governor such that any application login (do not know the login or user name in prior ) should be included to the specific workloadgroup of limiting the CPU Resources. Like Example I have a application guy (either SQL login or windows ) asking for select access. So I should go ahead and allocate that user to particular workload group. So should I proceed to write dynamical storedprocedure to accept the login and allocate it to workload group. IF so..how my classifier function should look like. Any ideas or suggestions are requested. Thank you.
You wouldn't need to write a dynamic stored procedure to deal with unknown logins. Take this example where we have defined 2 workload groups : LimitedResources and UnLimitedResources. Lets assume that only members of the sysadmin role have unlimited access. Your classifer function would then look something like CREATE FUNCTION MyClassifierFunction() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN DECLARE @grp_name as SYSNAME IF IS_SRVROLEMEMBER('sysadmin') = 1 SET @grp_name = 'UnLimitedResources' ELSE SET @grp_name = 'LimitedResources' RETURN @grp_name END Then you only have to manage those logins that have higher levels of access, instead of every other possible login. You can also use Windows groups or database roles - in this case use the `IS_MEMBER()` function. Review this [page on MSDN/BOL] for a full list of the functions available to you :