January 14, 2013 at 10:01 am
I am setting up the Resource Governor on one of my servers and it creates a function in the master database. The function is used to point Users into a specific Resource Pool, therfore the user must exist in the Master DB. The question is what are some negative effects that may occur by adding users to my Master DB....Here is the Function so its easier to understand exactly what I am relating to...
ALTER FUNCTION [dbo].[UDFClassifier]()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'SPECIALIZED\SG_DataManagementNormalAccounts')
SET @WorkloadGroup = 'DataManagementGroup'
ELSE IF (SUSER_NAME() = 'SPECIALIZED\SG_Analytics_DBA')
SET @WorkloadGroup = 'ReportingGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
January 14, 2013 at 1:18 pm
rynmthw3 (1/14/2013)
I am setting up the Resource Governor on one of my servers and it creates a function in the master database. The function is used to point Users into a specific Resource Pool, therfore the user must exist in the Master DB. The question is what are some negative effects that may occur by adding users to my Master DB....Here is the Function so its easier to understand exactly what I am relating to...ALTER FUNCTION [dbo].[UDFClassifier]()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'SPECIALIZED\SG_DataManagementNormalAccounts')
SET @WorkloadGroup = 'DataManagementGroup'
ELSE IF (SUSER_NAME() = 'SPECIALIZED\SG_Analytics_DBA')
SET @WorkloadGroup = 'ReportingGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
I am not sure you need to have a User created in master for each Login. All non-sysadmins authenticate into master as guest by default. If you are having trouble with permissions you can consider granting them to guest. SUSER_NAME() returns the Server Login info so I do not think you will have an issue with leveraging guest as long as you're looking for the Login name.
If you do not like the idea of granting to guest then creating Users in master will not hurt you at all.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 14, 2013 at 1:28 pm
Yes but to restict access to one specific login they would have to be in the master correct? What I am looking to do is allow my group to have rights to 100%cpu and memory and limit the Analytic group to just 40%...but if it is a different user then they would be directed to the default Resource Pool and those users will have 50%
January 14, 2013 at 3:05 pm
rynmthw3 (1/14/2013)
Yes but to restict access to one specific login they would have to be in the master correct?
I do not think so. SUSER_NAME() does not have a restriction related to Database Users. The function is poorly named. It returns Server Login information.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 14, 2013 at 4:09 pm
I do not think so. SUSER_NAME() does not have a restriction related to Database Users. The function is poorly named. It returns Server Login information.[/quote]
What if rather than SUSER_Name()= I use USER_NAME()=
Also when I declare my workloadGroup as SysName I feel like this is causing an issue.... I have played around with it for sometime and it doesnt seem that it is realizing that I'm telling it that these users belong in these Workload Groups...after I configure this I am running queries that are really CPU intensive to lock the server up and come back with an error message to see which pool it is placing me in and it has been the 'default' everytime
January 15, 2013 at 11:05 pm
rynmthw3 (1/14/2013)
I do not think so. SUSER_NAME() does not have a restriction related to Database Users. The function is poorly named. It returns Server Login information.
What if rather than SUSER_Name()= I use USER_NAME()=
SUSER_NAME() should be fine.
In looking at your code a little closer...is SPECIALIZED\SG_DataManagementNormalAccounts a Windows Group? If you are you trying to use a Windows Group name to do the classification that will not work with SUSER_NAME() alone. You would need to use SUSER_NAME() in conjunction with sys.xp_logininfo to verify group membership (assuming SPECIALIZED\SG_DataManagementNormalAccounts had a login in the SQL Server).
Also when I declare my workloadGroup as SysName I feel like this is causing an issue.... I have played around with it for sometime and it doesnt seem that it is realizing that I'm telling it that these users belong in these Workload Groups...after I configure this I am running queries that are really CPU intensive to lock the server up and come back with an error message to see which pool it is placing me in and it has been the 'default' everytime
You do not have to run a query to check that the classifier function is working, you just need to log in.
edit: establish assumption for using sys.xp_logininfo
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 16, 2013 at 12:19 am
I just came back to this and found that you cannot use a temp table in a classifier function, meaning I cannot see a way to use sys.xp_logininfo directly to check group membership.
What you might do is have a proc run periodically to capture the results of sys.xp_logininfo for your groups into a table in master and then refer to that table in your classifier function.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply