Sql Server : What permissions does my account have on the database?

Interesting one today:

Recently, we covered a post on detailed permissions, in the entire database, on all resources assigned to all the principals. Today we’ll see a slight variation of it, that is more focused on current user.

What permissions does my account have in the database?

Sql Server provides a built-in function call ‘fn_my_permissions‘ that lists all permissions current user has on a given object. Once we wrap this into a loop and query all the objects in the database and PIVOT the result set, we get a usable & actionable list of permissions on all objects for the current user.

To extend this further, we could run the same script with ‘EXECUTE AS USER = ”‘ to capture permissions assigned to any other user (of course, you’d first need permissions to be able to impersonate that user)

--
--	What are my permissions
--

--EXECUTE AS USER = 'kelly'
--SELECT SYSTEM_USER, SESSION_USER

--
--	Variable declaration
--
DECLARE @v_Sql VARCHAR(MAX)
DECLARE @Tbl_Perms TABLE (entity_name VARCHAR(100) NULL, Type VARCHAR(50) NULL, permission_name VARCHAR(50) NULL, Permission_value INT NULL)

--
--	Generate Sql Query to get permissions on all the objects
--
SELECT @v_Sql = ''

SELECT @v_Sql = @v_Sql + 'SELECT entity_name, ''' + type_desc + ''' AS [Type], permission_name, 1 AS [Permission_value] FROM fn_my_permissions(''' + s.name + '.' + o.name + ''',''object'') WHERE subentity_name IS NULL OR subentity_name = '''' UNION '
FROM sys.objects AS O
INNER JOIN sys.schemas AS S
	ON O.schema_id = S.schema_id
WHERE O.type IN	(	  'U'		--	USER_TABLE
					, 'V'		--	VIEW
					, 'P'		--	SQL_STORED_PROCEDURE
					, 'FN'		--	SQL_SCALAR_FUNCTION
					, 'IF'		--	SQL_INLINE_TABLE_VALUED_FUNCTION
					, 'TF'		--	SQL_TABLE_VALUED_FUNCTION
					, 'TR'		--	SQL_TRIGGER
					, 'S'		--	SYSTEM_TABLE
					, 'SN'		--	SYNONYM
				)
--	AND O.SCHEMA_ID = 1
ORDER BY O.type ASC, O.name ASC

--
--	Add Certifcate Permissions
--
SELECT @v_Sql = @v_Sql + ' SELECT entity_name, ''Certificate'' AS [Type], permission_name, 1 AS [Permission_value] FROM fn_my_permissions(''' + name + ''', ''Certificate'') UNION '
FROM sys.certificates

--
--	Add symmetric_keys Permissions
--
SELECT @v_Sql = @v_Sql + ' SELECT entity_name, ''Symmetric Keys'' AS [Type], permission_name, 1 AS [Permission_value] FROM fn_my_permissions(''' + name + ''', ''Symmetric Key'') UNION '
FROM sys.symmetric_keys

--
--	Add asymmetric_keys Permissions
--
SELECT @v_Sql = @v_Sql + ' SELECT entity_name, ''Asymmetric Keys'' AS [Type], permission_name, 1 AS [Permission_value] FROM fn_my_permissions(''' + name + ''', ''Asymmetric Key'') UNION '
FROM sys.asymmetric_keys

--
--	Add Server and Database permissions
--
SELECT @v_Sql = @v_Sql + ' SELECT entity_name, ''Database'' AS [Type], permission_name, 1 AS [Permission_value] FROM fn_my_permissions(NULL, ''Database'') UNION SELECT entity_name, ''Server'' AS [Type], permission_name, 1 AS [Permission_value] FROM fn_my_permissions(NULL, ''Server'')'

--PRINT @v_Sql

--
--	Insert into a temp table
--
INSERT INTO @Tbl_Perms
EXEC (@v_Sql)

--
--	Pivot into a matrix
--
; WITH Perms AS
	(
		SELECT entity_name, [Type], [SELECT], [INSERT], [UPDATE], [DELETE], [EXECUTE], [ALTER], [VIEW DEFINITION], [CONTROL], [REFERENCES], [TAKE OWNERSHIP], [CONNECT SQL], [ALTER TRACE], [VIEW ANY DATABASE], [CONTROL SERVER], [CONNECT]
		FROM (SELECT entity_name, [Type], permission_name, Permission_Value FROM @Tbl_Perms) AS S
		PIVOT
			(
				MAX(S.Permission_Value)
				FOR S.Permission_name IN ([SELECT], [INSERT], [UPDATE], [DELETE], [EXECUTE], [ALTER], [VIEW DEFINITION], [CONTROL], [REFERENCES], [TAKE OWNERSHIP], [CONNECT SQL], [ALTER TRACE], [VIEW ANY DATABASE], [CONTROL SERVER], [CONNECT])
			) AS PVT
	)

--
--	Returning it all together
--
SELECT * FROM Perms
ORDER BY [Type] ASC
GO

--
--	Execute as Identified User above
--
--REVERT
--SELECT SYSTEM_USER, SESSION_USER
GO

The result set is a PIVOTed table with clear list of permissions on each object.

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Sql Server : List of Detailed Permissions on Each Object

Interesting one today:

Quite often, we need a quick way to check if correct permissions are granted on the database objects. Since there could be thousands of such objects with hundreds of different possible permissions, this gets a bit daunting.

In the past, we’ve seen some dynamic between Roles, Logins, Users & Permissions. Today, we’ll look into a quick way to a query to retrieve permissions on all objects for all principals; Then we can filter further and focus on the objects/logins we need.

--
-- Detailed Permissions<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;">&#65279;</span>
--
SELECT    P.class_desc                          AS [Permission_Category]
		, P.grantee_principal_id				AS [Principal_ID]
		, L.name                                AS [Principal_Name]
		, L.type_desc							AS [Principal_Type]
		, P.major_id							AS [ID_of_Resource]
		, CASE P.class_desc
			WHEN 'CERTIFICATE' THEN C.name
			WHEN 'SYMMETRIC_KEYS' THEN S.name
			WHEN 'TYPE' THEN T.name
			ELSE O.name
		  END									AS [ObjectName]
		, O.type_desc							AS [Detailed_Object_Type]
		, P.state_desc                          AS [Permission]
		, G.name                                AS [Who_Gave_This_Permission]

FROM sys.database_permissions AS P
INNER JOIN sys.database_principals AS L
       ON L.principal_id = P.grantee_principal_id
LEFT OUTER JOIN sys.database_principals AS G
       ON G.principal_id = P.grantor_principal_id
LEFT OUTER JOIN sys.objects AS O
       ON O.object_id = P.major_id
LEFT OUTER JOIN sys.symmetric_keys AS S
       ON S.symmetric_key_id = P.major_id
LEFT OUTER JOIN sys.Certificates AS C
       ON C.certificate_id = P.major_id
LEFT OUTER JOIN sys.types AS T
	ON T.user_type_id = P.major_id
WHERE P.major_id &gt;= 0      --     Ignore system objects
ORDER BY P.class_desc, L.name ASC
GO

Hope this helps,
_Sqltimes

Read Full Post »