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.
Read Full Post »
Playing with database servers... 