Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is adding a User to the master Database a bad Idea? Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:38 PM
Points: 10, Visits: 103
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
Post #1406825
Posted Monday, January 14, 2013 1:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1406923
Posted Monday, January 14, 2013 1:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:38 PM
Points: 10, Visits: 103
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%
Post #1406931
Posted Monday, January 14, 2013 3:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1406956
Posted Monday, January 14, 2013 4:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:38 PM
Points: 10, Visits: 103
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

Post #1406975
Posted Tuesday, January 15, 2013 11:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1407607
Posted Wednesday, January 16, 2013 12:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1407630
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse