Sql Server: Query to list all the permissions of any login
- Posted by Sqltimes
- On March 15, 2014
- 0 Comments
Another quick one today:
A lot too often, we find ourselves in a situation where we are pulled into support a new system or help troubleshoot a new database environment. There are a series of checks, I prefer to run when a new system is added to my list of database environments for our team.
Often, when I get pulled into troubleshooting a production situation, among other things I perform a series of checks. Depending on the nature of the problem description, sometimes checking ‘application login’ permissions details database helps. It is possible that the application login is assigned to appropriate roles, but as application grows and evolves it goes through a lot of changes; Some of those changes result in some seemingly minor permission aspects falling through the cracks. Some, but not all.
In these situation, I use the below script to give me a detailed listing of permissions that this account has. A quick glance of this helps me rule out a few things. Below is the script:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *---- Purpose : List permissions on all the objects in the given database for the current user-- Author : SqlTimes on Oct 28, 2009---- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *---- Start of the script------ Run as 'user'-- --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 ) ORDER BY O.type ASC, O.name ASC---- 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---- End of the script-- |
Hope this helps,
_Sqltimes
0 Comments