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