SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is adding a User to the master Database a bad Idea?


Is adding a User to the master Database a bad Idea?

Author
Message
rynmthw3
rynmthw3
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 114
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
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14869 Visits: 14396
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
rynmthw3
rynmthw3
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 114
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%
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14869 Visits: 14396
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
rynmthw3
rynmthw3
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 114
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
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14869 Visits: 14396
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
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14869 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search