Rättigheter för användarna

När det gäller rättigheter för användarna i databaserna finns det ingen möjlighet att se detta i SQL Server Management Studio på ett överskådligt sätt. Nedan är ett exempel som bättre visar de olika användarna och dess rättigheter och där vi kan se några av kolumnerna från SQL koden som följer längre ned.

Att de råder förrvirring om vilka rättighet man egentligen behöver för de olika användarna och dess roller möter vi ganska ofta. Ofta verkar det ha slutat med att man sätter sysadmin och detta är förstås inte bra, då det innebär totalt frihet att göra allt på servern.

Mer om databasroller kan man läsa här och om serverroller finns information här.

Nedan följer koden som visar de olika användarna för hela servern och dess databaser och den kan man förstås modifiera för att passa ens smak och tycke bättre

DECLARE @DB_USers TABLE
(DatabaseName sysname, UserName sysname, LoginType sysname, DBRoleName varchar(max),
	CreateDate datetime, ModifyDate datetime, ServerRoleName varchar(max))

INSERT @DB_USers
EXEC sp_MSforeachdb

'USE [?]

SELECT ''?'', Users.Name, Users.Type_Desc,  UserDBRole.DBRoleName, Users.Create_date, 
	ISNULL(UserDBRole.Modify_date, Users.Modify_date), ServerRole.name
FROM sys.server_principals Users

OUTER APPLY
(
SELECT
	prin.name, 
	ISNULL(USER_NAME(mem.role_principal_id),'''') AS DBRoleName,
	Create_date, Modify_date
FROM sys.database_principals prin
	LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
	prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'' AND
	prin.name COLLATE Finnish_Swedish_CI_AS = Users.Name COLLATE Finnish_Swedish_CI_AS
) UserDBRole

OUTER APPLY
(
	SELECT principals.name
	FROM
		sys.server_principals principals
		INNER JOIN sys.server_role_members members ON 
			principals.principal_id = members.role_principal_id
		INNER JOIN sys.server_principals principals2 ON 
			principals2.principal_id = members.member_principal_id
	WHERE principals2.name  = Users.Name
) ServerRole

WHERE NOT Users.Name IN (''NT AUTHORITYSYSTEM'')
	AND (NOT ServerRole.name IS NULL OR NOT UserDBRole.name IS NULL)' 

SELECT
	DatabaseName, LoginType, UserName, CreateDate AS CreateDate, ModifyDate,

	STUFF(
	(
	SELECT DISTINCT ',' + CONVERT(VARCHAR(500),ServerRoleName)
	FROM @DB_USers user2
	WHERE
		user1.DatabaseName=user2.DatabaseName AND user1.UserName=user2.UserName 
	FOR XML PATH('')
	)
	,1,1,'') AS ServerRoleName	,
	
	STUFF(
	(
	SELECT DISTINCT ',' + CONVERT(VARCHAR(500),DBRoleName)
	FROM @DB_USers user2
	WHERE
		user1.DatabaseName=user2.DatabaseName AND user1.UserName=user2.UserName
	FOR XML PATH('')
	)
	,1,1,'') AS DBRoleName

FROM @DB_USers user1

GROUP BY DatabaseName, UserName ,LoginType ,CreateDate ,ModifyDate
ORDER BY DatabaseName, LoginType, UserName