Olá, pessoal!
Bom dia.
Neste post, irei demonstrar como verificar as permissões de um usuário e/ou database no SQL Server, além de permitir facilmente remover esses acessos ou conceder novamente. Caso você queira é replicar as permissões de um usuário entre instâncias diferentes, veja se o artigo SQL Server – Como copiar/replicar as permissões de um usuário pode te ajudar.
Utilizando a SP de sistema, sp_helprotect
Através do uso da stored procedure sp_helprotect, podemos facilmente listar as permissões de um determinado usuário ou objeto:
1 2 3 | -- Verificando as permissões do usuário "Usuario_Teste" EXEC Protheus_Producao.dbo.sp_helprotect @username = 'Usuario_Teste' |
1 2 3 | -- Verificando as permissões da tabela "SA1010" EXEC Protheus_Producao.dbo.sp_helprotect @name = 'SA1010' |
Utilizando consultas Transact SQL em views de catálogo
Uma forma muito interessante de se obter e trabalhar com essas informações que vimos na interface do SQL Server Managment Studio é utilizando queries SQL e views de catálogo do banco de dados SQL Server. Essas queries trazem mais informações que a sp de sistema sp_helprotect e assim como utilizando a SP, podemos trabalhar com os dados retornados.
Visualizar código-fonte 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 |
Utilizando uma Stored Procedure personalizada
Com um pouco mais de trabalho e refinamento, podemos utilizar uma Stored Procedure para essa finalidade. Podemos utilizar o retorno para gerar um relatório de acessos, gravar as informações para histórico, etc. Veja como é fácil:
Visualizar código-fonte da SP 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 |
Exemplos de utilização
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 |
Exemplo de retorno (primeiro exemplo)
Utilizando uma Stored Procedure com código de GRANT e REVOKE
Assim como a Stored Procedure anterior, essa versão é um pouco mais “compacta” e ainda gera os códigos T-SQL para conceder esse privilégio existe (caso você queira fazer um backup das permissões existentes) e também o código T-SQL para remover essa permissão.
Visualizar código da stpVerifica_Permissoes_Usuario 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 |
Exemplos de uso:
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 |
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 |