Reading Time: 15 minutes
Hello people!
Good day.
In this post, I will demonstrate how to verify the permissions of a user and / or database in SQL Server, as well as allowing you to easily remove these access or grant again. If you want to replicate a user's permissions between different instances, see if the article SQL Server - How to copy / replicate a user's permissions can help you.
Using system SP, sp_helprotect
Using the stored procedure sp_helprotect, we can easily list the permissions of a particular user or object:
Transact-SQL
| 1 2 3 | -- Verificando as permissões do usuário "Usuario_Teste" EXEC Protheus_Producao.dbo.sp_helprotect @username = 'Usuario_Teste' |
Result
Transact-SQL
| 1 2 3 | -- Verificando as permissões da tabela "SA1010" EXEC Protheus_Producao.dbo.sp_helprotect @name = 'SA1010' |
Result
Using Transact SQL Queries in Catalog Views
A very interesting way to get and work with this information that we saw in the SQL Server Managment Studio interface is by using SQL queries and SQL Server database catalog views. These queries bring more information than the sp_helprotect system sp and just like using SP we can work with the returned data.
View sourceTransact-SQL
| 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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | DECLARE @Ds_Usuario VARCHAR(100) = 'Usuario_Teste' -- Associação Usuário x Login IF (OBJECT_ID('tempdb..#Users_Logins') IS NOT NULL) DROP TABLE #Users_Logins SELECT C.name AS Ds_Login, B.name AS Ds_Usuario INTO #Users_Logins FROM sys.database_principals A WITH(NOLOCK) JOIN sys.sysusers B WITH(NOLOCK) ON A.principal_id = B.uid LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid WHERE A.type_desc != 'DATABASE_ROLE' AND (C.name = @Ds_Usuario OR B.name = @Ds_Usuario OR @Ds_Usuario IS NULL) -- Recupera o Login e o usuário DECLARE @Ds_Usuario_Recuperado VARCHAR(MAX), @Ds_Login_Recuperado VARCHAR(MAX) SELECT @Ds_Login_Recuperado = Ds_Login, @Ds_Usuario_Recuperado = Ds_Usuario FROM #Users_Logins -- Database Roles IF (OBJECT_ID('tempdb..#Database_Roles') IS NOT NULL) DROP TABLE #Database_Roles SELECT C.name AS Ds_Usuario, B.name AS Ds_Database_Role INTO #Database_Roles FROM sys.database_role_members A WITH(NOLOCK) JOIN sys.database_principals B WITH(NOLOCK) ON A.role_principal_id = B.principal_id JOIN sys.sysusers C WITH(NOLOCK) ON A.member_principal_id = C.uid WHERE (C.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL) -- Database Permissions IF (OBJECT_ID('tempdb..#Database_Permissions') IS NOT NULL) DROP TABLE #Database_Permissions SELECT A.class_desc AS Ds_Tipo_Permissao, A.permission_name AS Ds_Permissao, A.state_desc AS Ds_Operacao, B.name AS Ds_Usuario_Permissao, C.name AS Ds_Login_Permissao, D.name AS Ds_Objeto INTO #Database_Permissions FROM sys.database_permissions A WITH(NOLOCK) JOIN sys.sysusers B WITH(NOLOCK) ON A.grantee_principal_id = B.uid LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid LEFT JOIN sys.objects D WITH(NOLOCK) ON A.major_id = D.object_id WHERE A.major_id >= 0 AND (C.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR B.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL) -- Server roles IF (OBJECT_ID('tempdb..#Server_Roles') IS NOT NULL) DROP TABLE #Server_Roles SELECT B.name AS Ds_Usuario, C.name AS Ds_Server_Role INTO #Server_Roles FROM sys.server_role_members A WITH(NOLOCK) JOIN sys.server_principals B WITH(NOLOCK) ON A.member_principal_id = B.principal_id JOIN sys.server_principals C WITH(NOLOCK) ON A.role_principal_id = C.principal_id WHERE (B.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL) -- Server permissions IF (OBJECT_ID('tempdb..#Server_Permissions') IS NOT NULL) DROP TABLE #Server_Permissions SELECT A.class_desc AS Ds_Tipo_Permissao, A.state_desc AS Ds_Tipo_Operacao, A.permission_name AS Ds_Permissao, C.name AS Ds_Login, B.type_desc AS Ds_Tipo_Login INTO #Server_Permissions FROM sys.server_permissions A WITH(NOLOCK) JOIN sys.server_principals B WITH(NOLOCK) ON A.grantee_principal_id = B.principal_id LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid WHERE (C.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL) SELECT * FROM #Users_Logins SELECT * FROM #Database_Roles SELECT * FROM #Database_Permissions SELECT * FROM #Server_Roles SELECT * FROM #Server_Permissions |
Return of Queries
Using a Custom Stored Procedure
With a little more work and refinement, we can use a Stored Procedure for this purpose. We can use feedback to generate an access report, record the information for history, etc. See how easy it is:
View SP source codeTransact-SQL
| 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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 | USE [Auditoria] GO /****** Object: StoredProcedure [dbo].[stpVerifica_Permissoes] Script Date: 09/07/2015 15:18:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[stpVerifica_Permissoes] ( @Ds_Usuario VARCHAR(100) = NULL, @Ds_Database VARCHAR(100) = NULL, @Ds_Objeto VARCHAR(100) = NULL, @Nr_Tipo_Permissao SMALLINT = NULL, @Fl_Permissoes_Servidor BIT = 1 ) AS BEGIN SELECT @Ds_Usuario = ISNULL(@Ds_Usuario, ''), @Ds_Database = ISNULL(@Ds_Database, ''), @Ds_Objeto = ISNULL(@Ds_Objeto, '') DECLARE @Query VARCHAR(MAX) ---------------------------------------------------------------------------------------- -- ASSOCIAÇÃO USUÁRIO X LOGIN ---------------------------------------------------------------------------------------- IF (OBJECT_ID('tempdb..#Users_Logins') IS NOT NULL) DROP TABLE #Users_Logins CREATE TABLE #Users_Logins ( Ds_Database VARCHAR(100), Ds_Login VARCHAR(100), Ds_Usuario VARCHAR(100) ) IF (@Nr_Tipo_Permissao = 0 OR @Nr_Tipo_Permissao IS NULL) BEGIN SET @Query = ' IF (''?'' = ''' + @Ds_Database + ''' OR ''' + @Ds_Database + ''' = '''') BEGIN USE [?] SELECT ''?'' AS Ds_Database, C.name AS Ds_Login, B.name AS Ds_Usuario FROM sys.database_principals A WITH(NOLOCK) JOIN sys.sysusers B WITH(NOLOCK) ON A.principal_id = B.uid LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid WHERE A.type_desc != ''DATABASE_ROLE'' AND (C.name = ''' + @Ds_Usuario + ''' OR B.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''') AND (C.name = ''' + @Ds_Objeto + ''' OR B.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''') END' INSERT INTO #Users_Logins EXEC master.dbo.sp_MSforeachdb @Query END ---------------------------------------------------------------------------------------- -- PERMISSÕES EM ROLES DE BANCO DE DADOS ---------------------------------------------------------------------------------------- IF (OBJECT_ID('tempdb..#Database_Roles') IS NOT NULL) DROP TABLE #Database_Roles CREATE TABLE #Database_Roles ( Ds_Database VARCHAR(100), Ds_Login VARCHAR(100), Ds_Usuario VARCHAR(100), Ds_Database_Role VARCHAR(100) ) IF (@Nr_Tipo_Permissao = 1 OR @Nr_Tipo_Permissao IS NULL) BEGIN SET @Query = ' IF (''?'' = ''' + @Ds_Database + ''' OR ''' + @Ds_Database + ''' = '''') BEGIN USE [?] SELECT ''?'' AS Ds_Database, D.name AS Ds_Login, C.name AS Ds_Usuario, B.name AS Ds_Role FROM sys.database_role_members A WITH(NOLOCK) JOIN sys.database_principals B WITH(NOLOCK) ON A.role_principal_id = B.principal_id JOIN sys.sysusers C WITH(NOLOCK) ON A.member_principal_id = C.uid LEFT JOIN sys.syslogins D WITH(NOLOCK) ON C.sid = D.sid WHERE (C.name = ''' + @Ds_Usuario + ''' OR D.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''') AND (B.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''') END' INSERT INTO #Database_Roles EXEC master.dbo.sp_MSforeachdb @Query END ---------------------------------------------------------------------------------------- -- PERMISSÕES A NÍVEL DE BANCO DE DADOS ---------------------------------------------------------------------------------------- IF (OBJECT_ID('tempdb..#Database_Permissions') IS NOT NULL) DROP TABLE #Database_Permissions CREATE TABLE #Database_Permissions ( Ds_Database VARCHAR(100), Ds_Tipo_Permissao VARCHAR(60), Ds_Permissao VARCHAR(128), Ds_Operacao VARCHAR(60), Ds_Login_Permissao VARCHAR(100), Ds_Usuario_Permissao VARCHAR(100), Ds_Objeto VARCHAR(100) ) IF (@Nr_Tipo_Permissao = 2 OR @Nr_Tipo_Permissao IS NULL) BEGIN SET @Query = ' IF (''?'' = ''' + @Ds_Database + ''' OR ''' + @Ds_Database + ''' = '''') BEGIN USE [?] SELECT ''?'' AS Ds_Database, A.class_desc AS Ds_Tipo_Permissao, A.permission_name AS Ds_Permissao, A.state_desc AS Ds_Operacao, C.name AS Ds_Login_Permissao, B.name AS Ds_Usuario_Permissao, D.name AS Ds_Objeto FROM sys.database_permissions A WITH(NOLOCK) JOIN sys.sysusers B WITH(NOLOCK) ON A.grantee_principal_id = B.uid LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid LEFT JOIN sys.objects D WITH(NOLOCK) ON A.major_id = D.object_id WHERE A.major_id >= 0 AND (B.name = ''' + @Ds_Usuario + ''' OR C.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''') AND (D.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''') END' INSERT INTO #Database_Permissions EXEC master.dbo.sp_MSforeachdb @Query END ---------------------------------------------------------------------------------------- -- PERMISSÕES EM ROLES DE SISTEMA ---------------------------------------------------------------------------------------- IF (OBJECT_ID('tempdb..#Server_Roles') IS NOT NULL) DROP TABLE #Server_Roles CREATE TABLE #Server_Roles ( Ds_Usuario VARCHAR(100), Ds_Server_Role VARCHAR(100) ) IF ((@Fl_Permissoes_Servidor = 1 AND @Nr_Tipo_Permissao IS NULL) OR @Nr_Tipo_Permissao = 3) BEGIN SET @Query = ' SELECT B.name AS Ds_Usuario, C.name AS Ds_Role FROM sys.server_role_members A WITH(NOLOCK) JOIN sys.server_principals B WITH(NOLOCK) ON A.member_principal_id = B.principal_id JOIN sys.server_principals C WITH(NOLOCK) ON A.role_principal_id = C.principal_id WHERE (B.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''') AND (B.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')' INSERT INTO #Server_Roles EXEC (@Query) END ---------------------------------------------------------------------------------------- -- PERMISSÕES A NÍVEL DE SERVIDOR ---------------------------------------------------------------------------------------- IF (OBJECT_ID('tempdb..#Server_Permissions') IS NOT NULL) DROP TABLE #Server_Permissions CREATE TABLE #Server_Permissions ( Ds_Tipo_Permissao VARCHAR(60), Ds_Tipo_Operacao VARCHAR(60), Ds_Permissao VARCHAR(128), Ds_Login VARCHAR(100), Ds_Tipo_Login VARCHAR(100) ) IF ((@Fl_Permissoes_Servidor = 1 AND @Nr_Tipo_Permissao IS NULL) OR @Nr_Tipo_Permissao = 4) BEGIN SET @Query = ' SELECT A.class_desc AS Ds_Tipo_Permissao, A.state_desc AS Ds_Tipo_Operacao, A.permission_name AS Ds_Permissao, C.name AS Ds_Login, B.type_desc AS Ds_Tipo_Login FROM sys.server_permissions A WITH(NOLOCK) JOIN sys.server_principals B WITH(NOLOCK) ON A.grantee_principal_id = B.principal_id LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid WHERE (C.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''') AND (C.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')' INSERT INTO #Server_Permissions EXEC(@Query) END ---------------------------------------------------------------------------------------- -- DEFINE AS SAÍDAS ---------------------------------------------------------------------------------------- SELECT 0 AS Id_Nivel_Permissao, 'User_Login' AS Ds_Nivel_Permissao, Ds_Database, NULL AS Ds_Tipo_Permissao, 'LOGIN' AS Ds_Permissao, 'GRANT' AS Ds_Operacao, Ds_Login, Ds_Usuario, NULL AS Ds_Objeto FROM #Users_Logins UNION ALL SELECT 1 AS Id_Nivel_Permissao, 'Database_Role' AS Ds_Nivel_Permissao, Ds_Database, NULL AS Ds_Tipo_Permissao, Ds_Database_Role AS Ds_Permissao, 'GRANT' AS Ds_Operacao, Ds_Login, Ds_Usuario, NULL AS Ds_Objeto FROM #Database_Roles UNION ALL SELECT 2 AS Id_Nivel_Permissao, 'Database_Permission' AS Ds_Nivel_Permissao, Ds_Database, Ds_Tipo_Permissao, Ds_Permissao, Ds_Operacao, Ds_Login_Permissao, Ds_Usuario_Permissao, Ds_Objeto FROM #Database_Permissions UNION ALL SELECT 3 AS Id_Nivel_Permissao, 'Server_Role' AS Ds_Nivel_Permissao, NULL AS Ds_Database, NULL AS Ds_Tipo_Permissao, Ds_Server_Role AS Ds_Permissao, 'GRANT' AS Ds_Operacao, Ds_Usuario AS Ds_Login, NULL AS Ds_Usuario, @@SERVERNAME AS Ds_Objeto FROM #Server_Roles UNION ALL SELECT 4 AS Id_Nivel_Permissao, 'Server_Permission' AS Ds_Nivel_Permissao, NULL AS Ds_Database, Ds_Tipo_Permissao, Ds_Permissao, Ds_Tipo_Operacao AS Ds_Operacao, Ds_Login, NULL AS Ds_Usuario, @@SERVERNAME AS Ds_Objeto FROM #Server_Permissions ORDER BY 1, 3, 7, 8, 9 END |
Usage examples
Transact-SQL
| 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 | -- Verifica todas as permissões do usuário 'Usuario_Teste' na instância EXEC dbo.stpVerifica_Permissoes @Ds_Usuario = 'Usuario_Teste' -- Verifica todas as permissões da tabela 'SA1010' no database 'Protheus_Producao' EXEC dbo.stpVerifica_Permissoes @Ds_Database = 'Protheus_Producao', @Ds_Objeto = 'SA1010' -- Verifica as roles de database do usuário 'Usuario_Teste' em todos os bancos EXEC dbo.stpVerifica_Permissoes @Ds_Usuario = 'Usuario_Teste', -- varchar(100) @Ds_Database = NULL, -- varchar(100) @Ds_Objeto = NULL, @Nr_Tipo_Permissao = 1, @Fl_Permissoes_Servidor = 0 -- Não -- Verifica as permissões a nível de Database do usuário 'Usuario_Teste' EXEC dbo.stpVerifica_Permissoes @Ds_Usuario = 'Usuario_Teste', -- varchar(100) @Ds_Database = NULL, -- varchar(100) @Ds_Objeto = NULL, @Nr_Tipo_Permissao = 2, @Fl_Permissoes_Servidor = 0 -- Não -- Verifica as permissões do database 'Protheus_Producao' para todos os usuários EXEC dbo.stpVerifica_Permissoes @Ds_Usuario = NULL, -- varchar(100) @Ds_Database = 'Protheus_Producao', -- varchar(100) @Ds_Objeto = NULL, @Nr_Tipo_Permissao = 2, @Fl_Permissoes_Servidor = 0 -- Não -- Verifica as permissões a nível de sistema da instância EXEC dbo.stpVerifica_Permissoes @Nr_Tipo_Permissao = 4 -- Verifica os membros de roles de sistema da instância EXEC dbo.stpVerifica_Permissoes @Nr_Tipo_Permissao = 3 |
Return Example (First Example)
Using a Stored Procedure with GRANT and REVOKE Code
Like the previous Stored Procedure, this version is a little more "compact" and still generates the T-SQL codes to grant this privilege (if you want to make a backup of the existing permissions) and also the T-SQL code to remove this permission.
View stp's codeVerify_User_PermissionsTransact-SQL
| 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 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 | IF (OBJECT_ID('dbo.stpVerifica_Permissoes_Usuario') IS NULL) EXEC('CREATE PROCEDURE dbo.stpVerifica_Permissoes_Usuario AS SELECT 1') GO ALTER PROCEDURE [dbo].[stpVerifica_Permissoes_Usuario] @Ds_Database [varchar](50) = NULL, @Ds_Usuario [varchar](50) = NULL, @Fl_Objetos_Sistema BIT = 0, @Fl_Permissoes_Instancia BIT = 1, @Fl_Permissoes_Banco BIT = 1 AS BEGIN -- DECLARE @Ds_Database [VARCHAR](50) = NULL, @Ds_Usuario [VARCHAR](50) = NULL, @Fl_Objetos_Sistema BIT = 0, @Fl_Permissoes_Instancia BIT = 1, @Fl_Permissoes_Banco BIT = 1; DECLARE @query VARCHAR(MAX); SET @query = ' SELECT DISTINCT ' + CHAR( 39 ) + '?' + CHAR( 39 ) + ' [Database], C.name [Schema], COALESCE(B.name, E.name) [Object], COALESCE(B.type_desc, E.type_desc) AS [object_type], D.name username, A.type permissions_type, A.permission_name, A.state permission_state, A.state_desc, (CASE WHEN B.is_ms_shipped = 1 OR E.[object_id] IS NOT NULL OR B.name IN (''sysdiagrams'', ''sp_upgraddiagrams'', ''sp_helpdiagrams'', ''sp_helpdiagramdefinition'', ''sp_creatediagram'', ''sp_renamediagram'', ''sp_alterdiagram'', ''sp_dropdiagram'', ''fn_diagramobjects'') THEN 1 ELSE 0 END) AS [system_object], (CASE WHEN A.class = 1 THEN ' + CHAR( 39 ) + 'USE [?]; REVOKE ' + CHAR( 39 ) + ' + A.permission_name + ' + CHAR( 39 ) + ' ON [' + CHAR( 39 ) + ' + C.name + ' + CHAR( 39 ) + '].[' + CHAR( 39 ) + ' + COALESCE(B.name, C.name) + ' + CHAR( 39 ) + '] FROM [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS ELSE ' + +CHAR( 39 ) + 'USE [?]; REVOKE ' + CHAR( 39 ) + ' + A.permission_name + ' + CHAR( 39 ) + ' FROM [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS END) AS remover, (CASE WHEN A.class = 1 THEN ' + CHAR( 39 ) + 'USE [?]; ' + CHAR( 39 ) + ' + A.state_desc + '' '' + A.permission_name + ' + CHAR( 39 ) + ' ON [' + CHAR( 39 ) + ' + C.name + ' + CHAR( 39 ) + '].[' + CHAR( 39 ) + ' + COALESCE(B.name, C.name) + ' + CHAR( 39 ) + '] TO [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS ELSE ' + CHAR( 39 ) + 'USE [?]; ' + CHAR( 39 ) + ' + A.state_desc + '' '' + A.permission_name + ' + CHAR( 39 ) + ' TO [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS END) AS conceder FROM [?].sys.database_permissions A WITH(NOLOCK) LEFT JOIN [?].sys.objects B WITH(NOLOCK) ON A.major_id = B.object_id LEFT JOIN [?].sys.schemas C WITH(NOLOCK) ON B.schema_id = C.schema_id JOIN [?].sys.database_principals D WITH(NOLOCK) ON A.grantee_principal_id = D.principal_id LEFT JOIN [?].sys.system_objects E WITH(NOLOCK) ON A.major_id = E.object_id WHERE ' + ( CASE WHEN @Ds_Usuario IS NULL THEN '1 = 1' ELSE 'D.name LIKE ''' + ISNULL( @Ds_Usuario, '' ) + '''' END ) + ' AND ( (COALESCE(B.name, E.name) IS NOT NULL AND A.class = 1) OR (A.class NOT IN (1, 6)) ) ORDER BY 1, 2, 3, 5'; PRINT @query; DECLARE @Object_Permissions TABLE ( [database] sysname, [schema] sysname NULL, [object] sysname NULL, [object_type] sysname NULL, [username] sysname, [permission_type] sysname, [permission_name] sysname, [permission_state] sysname, [state_desc] sysname, [system_object] BIT, [remover] VARCHAR(MAX), [conceder] VARCHAR(MAX) ); IF ( @Fl_Permissoes_Banco = 1 ) BEGIN INSERT INTO @Object_Permissions EXEC master.dbo.sp_MSforeachdb @query; END; -- Roles DECLARE @Role_Permissions TABLE ( DBName sysname, UserName sysname, LoginType sysname, DefaultUser BIT, AssociatedRole VARCHAR(MAX), create_date DATETIME, modify_date DATETIME, grant_permission VARCHAR(MAX), revoke_permission VARCHAR(MAX) ); IF ( @Fl_Permissoes_Banco = 1 ) BEGIN INSERT @Role_Permissions EXEC master.dbo.sp_MSforeachdb' SELECT DISTINCT ''?'' AS DB_Name, prin.name AS UserName, prin.type_desc AS LoginType, (CASE WHEN prin.principal_id < 5 THEN 1 ELSE 0 END) AS default_user, role.[name] AS AssociatedRole, prin.create_date, prin.modify_date, ''USE ['' + ''?'' + '']; ALTER ROLE ['' + role.[name] + ''] ADD MEMBER ['' + prin.[name] + ''];'' AS grant_permission, ''USE ['' + ''?'' + '']; ALTER ROLE ['' + role.[name] + ''] DROP MEMBER ['' + prin.[name] + ''];'' AS revoke_permission FROM [?].sys.database_principals prin WITH(NOLOCK) JOIN [?].sys.database_role_members mem WITH(NOLOCK) ON prin.principal_id = mem.member_principal_id JOIN [?].sys.database_principals role WITH(NOLOCK) ON mem.role_principal_id = role.principal_id WHERE prin.sid IS NOT NULL AND prin.principal_id > 4 AND prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'' ' ; END; DECLARE @Tabela_Final TABLE ( [database] NVARCHAR(128), [schema] NVARCHAR(128), [object] NVARCHAR(128), [permission_type] VARCHAR(19), [system_object] BIT, [username] NVARCHAR(128), [object_type] NVARCHAR(128), [permission_name] NVARCHAR(MAX), [read_only] BIT, [state_desc] NVARCHAR(128), [remover] VARCHAR(MAX), [conceder] VARCHAR(MAX) ); IF ( @Fl_Permissoes_Banco = 1 ) BEGIN INSERT INTO @Tabela_Final SELECT DISTINCT [database], [schema], [object], ( CASE WHEN object_type IS NULL THEN 'DATABASE_PERMISSION' ELSE 'DATABASE_OBJECT' END ) AS [permission_type], system_object, username, object_type, [permission_name], ( CASE WHEN object_type = 'SQL_SCALAR_FUNCTION' OR [permission_name] LIKE 'VIEW %' OR [permission_name] IN ( 'SELECT', 'CONNECT', 'REFERENCES', 'SHOWPLAN' ) THEN 1 ELSE 0 END ) AS [read_only], state_desc, remover, conceder FROM @Object_Permissions WHERE ( [username] LIKE @Ds_Usuario OR @Ds_Usuario IS NULL ) AND ( [database] = @Ds_Database OR @Ds_Database IS NULL ) AND [username] <> 'dbo' AND ( ( @Fl_Objetos_Sistema = 0 AND system_object = 0 ) OR @Fl_Objetos_Sistema = 1 ) UNION ALL SELECT DISTINCT DBName AS [database], NULL AS [schema], NULL AS [object], 'DATABASE_ROLE' AS [object_type], DefaultUser AS system_object, UserName AS username, LoginType AS permission_type, ISNULL( NULLIF(AssociatedRole, ''), 'public' ) AS [permission_name], ( CASE WHEN AssociatedRole IN ( 'db_datareader', 'SQLAgentUserRole', 'SQLAgentReaderRole' ) THEN 1 ELSE 0 END ) AS [read_only], 'GRANT' AS [state_desc], revoke_permission AS remover, grant_permission AS conceder FROM @Role_Permissions WHERE ( [UserName] LIKE @Ds_Usuario OR @Ds_Usuario IS NULL ) AND ( [DBName] = @Ds_Database OR @Ds_Database IS NULL ) AND [UserName] <> 'dbo' AND ( ( @Fl_Objetos_Sistema = 0 AND DefaultUser = 0 ) OR @Fl_Objetos_Sistema = 1 ); END; IF ( @Fl_Permissoes_Instancia = 1 ) BEGIN INSERT INTO @Tabela_Final SELECT DISTINCT NULL AS [DB_Name], NULL AS [schema], NULL AS [object], 'SERVER_PERMISSION' AS [permission_type], ( CASE WHEN A.principal_id < 10 THEN 1 ELSE 0 END ) AS system_object, A.[name] AS [username], A.[type_desc] AS [object_type], B.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AI AS [permission_name], ( CASE WHEN B.[permission_name] IN ( 'CONNECT SQL' ) OR B.[permission_name] LIKE 'VIEW %' THEN 1 ELSE 0 END ) AS [read_only], B.state_desc, 'USE [master]; REVOKE ' + B.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AI + ' FROM [' + A.[name] + '];' AS revoke_permission, 'USE [master]; GRANT ' + B.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AI + ' TO [' + A.[name] + '];' AS grant_permission FROM sys.server_principals A WITH ( NOLOCK ) JOIN sys.server_permissions B WITH ( NOLOCK ) ON A.principal_id = B.grantee_principal_id WHERE A.[sid] IS NOT NULL AND A.is_disabled = 0 AND A.[type] <> 'C' -- CERTIFICATE_MAPPED_LOGIN AND A.[name] NOT LIKE 'NT SERVICE\%' AND A.[name] NOT LIKE 'NT AUTHORITY\%' AND A.[name] NOT LIKE 'BUILTIN\%' AND ( A.[name] LIKE @Ds_Usuario OR @Ds_Usuario IS NULL ) UNION ALL SELECT DISTINCT NULL AS [DB_Name], NULL AS [schema], NULL AS [object], 'SERVER_ROLE' AS [permission_type], ( CASE WHEN A.principal_id < 10 THEN 1 ELSE 0 END ) AS system_object, A.[name] AS UserName, A.[type_desc] AS LoginType, C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI AS AssociatedRole, 0 AS [read_only], 'GRANT' AS [state_desc], 'USE [master]; ALTER SERVER ROLE [' + C.[name] + '] DROP MEMBER [' + A.[name] + '];' AS revoke_permission, 'USE [master]; ALTER SERVER ROLE [' + C.[name] + '] ADD MEMBER [' + A.[name] + '];' AS grant_permission FROM sys.server_principals A WITH ( NOLOCK ) JOIN sys.server_role_members B WITH ( NOLOCK ) ON A.principal_id = B.member_principal_id JOIN sys.server_principals C WITH ( NOLOCK ) ON B.role_principal_id = C.principal_id WHERE A.[sid] IS NOT NULL AND A.is_disabled = 0 AND A.[type] <> 'C' -- CERTIFICATE_MAPPED_LOGIN AND A.[name] NOT LIKE 'NT SERVICE\%' AND A.[name] NOT LIKE 'NT AUTHORITY\%' AND A.[name] NOT LIKE 'BUILTIN\%' AND ( A.[name] LIKE @Ds_Usuario OR @Ds_Usuario IS NULL ); END; SELECT * FROM @Tabela_Final ORDER BY [database], username, [schema], [object]; END |
Examples of use:
Transact-SQL
| 1 2 3 4 5 6 7 | -- Verifica as permissões de todos os usuários do database "dirceuresende" EXEC dbo.stpVerifica_Permissoes_Usuario @Ds_Database = 'dirceuresende', -- varchar(50) @Ds_Usuario = '', -- varchar(50) @Fl_Objetos_Sistema = 0, -- bit @Fl_Permissoes_Instancia = 0, -- bit @Fl_Permissoes_Banco = 1 -- bit |
Result:
Transact-SQL
| 1 2 3 4 5 6 7 | -- Verifica as permissões do usuário "teste" em todos os databases EXEC dbo.stpVerifica_Permissoes_Usuario @Ds_Database = NULL, -- varchar(50) @Ds_Usuario = 'teste', -- varchar(50) @Fl_Objetos_Sistema = 0, -- bit @Fl_Permissoes_Instancia = 0, -- bit @Fl_Permissoes_Banco = 1 -- bit |
Result:
Transact-SQL
| 1 2 3 4 5 6 7 | -- Verifica todas as permissões dos usuários "%teste%" em todos os databases e a nível de instância EXEC dbo.stpVerifica_Permissoes_Usuario @Ds_Database = NULL, -- varchar(50) @Ds_Usuario = '%teste%', -- varchar(50) @Fl_Objetos_Sistema = 0, -- bit @Fl_Permissoes_Instancia = 1, -- bit @Fl_Permissoes_Banco = 1 -- bit |
Result:
Transact-SQL
| 1 2 3 4 5 6 7 | -- Verifica todas as permissões, de todos os usuários da instância EXEC dbo.stpVerifica_Permissoes_Usuario @Ds_Database = NULL, -- varchar(50) @Ds_Usuario = NULL, -- varchar(50) @Fl_Objetos_Sistema = 1, -- bit @Fl_Permissoes_Instancia = 1, -- bit @Fl_Permissoes_Banco = 1 -- bit |
Result:
Using a Query
Another quick and convenient alternative to listing user permissions is by using a multi-UNION query to return role, system, and object permissions.
