June 29, 2015 at 9:45 pm
Hey,
I'm having a strange issue when using Resource Governor to assign users to a workload group based on their membership to a customer server role. If I add them individually everything works as expected, but if I add an AD group to the server role the classifier function doesn't assign the users to the desired workload group. Instead it just assigns them to the default one.
Has anyone come across this?
I'm just trying to avoid having to add multiple users (it could be a lot) to my custom server role.
July 1, 2015 at 4:33 am
Can you post the TSQL for your pools, groups and classifier?
July 1, 2015 at 9:40 am
Agree, need to see code. Not easy to guess here.
July 2, 2015 at 4:37 pm
Sorry I should have included that from the outset. Below is the code I'm using:
/****** Object: ResourcePool [pReadIntentAccess] Script Date: 3/07/2015 8:33:46 AM ******/
CREATE RESOURCE POOL [pReadIntentAccess] WITH(min_cpu_percent=0,
max_cpu_percent=10,
min_memory_percent=0,
max_memory_percent=10,
cap_cpu_percent=100,
AFFINITY SCHEDULER = AUTO
)
GO
/****** Object: WorkloadGroup [gReadIntentAccess] Script Date: 3/07/2015 8:32:50 AM ******/
CREATE WORKLOAD GROUP [gReadIntentAccess] WITH(group_max_requests=0,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=0) USING [pReadIntentAccess]
GO
CREATE FUNCTION [dbo].[fnReadIntentAccessClassifier]()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup SYSNAME
DECLARE @WhoAmI SYSNAME = ORIGINAL_LOGIN()
IF IS_SRVROLEMEMBER('ReadIntentAccess', @WhoAmi) = 1
SET @WorkloadGroup = 'gReadIntentAccess'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO
So the issue I'm having is if I add an AD group to the 'ReadIntentAccess' server role the individual users within that group aren't assigned to my custom workload group.
July 3, 2015 at 2:02 am
Ok I had some time to try replicate the issue: I created a server role called dataadmin. I then created a Windows group and put a login in there... called LOGINX which has the dataadmin permission role.
CREATE FUNCTION [dbo].[fnClassifier1]()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup SYSNAME
--DECLARE @WhoAmI SYSNAME = ORIGINAL_LOGIN()
IF IS_SRVROLEMEMBER('dataadmin') = 1
SET @WorkloadGroup = 'gAccess'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO
I then start a new session and
-- Run the below as LOGIN X
SELECT * FROM dbo.blanktable
see my attachment... it does go to the gAccess group when I login as LOGINX... Is that what you need?
July 5, 2015 at 4:56 pm
Yeah that's what I need.
I'll do some more testing so my function is similar to the one you tested.
July 5, 2015 at 5:20 pm
That seems to work perfectly!
Thanks for the help with that.
July 6, 2015 at 2:58 am
Chris-475469 (7/5/2015)
That seems to work perfectly!Thanks for the help with that.
you are very welcome!
July 6, 2015 at 12:26 pm
Just to explain what was at issue with the original script, check out this from the documentation of IS_SRVROLEMEMBER:
If a Windows login, such as Contoso\Mary5, is specified for login, IS_SRVROLEMEMBER returns NULL, unless the login has been granted or denied direct access to SQL Server.
So, since those logins had not been granted direct access, IS_SRVROLEMEMBER was returning NULL when you passed them in as the login parameter. In the revised script, you are not passing a login parameter, and as the above link goes on to explain, then it is able to resolve indirect memberships.
Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply