Warning: preg_match(): Unknown modifier 'b' in /mnt/datadisk/www/src/Controllers/PostController.php on line 206 Verificando Permisos, Privilegios y Accesos de un Usuario de Base de Datos en SQL Server — Dirceu ResendeSaltar al contenido
En esta publicación, demostraré cómo verificar los permisos de un usuario y/o base de datos en SQL Server, además de permitirle eliminar u otorgar fácilmente estos accesos nuevamente. Si desea replicar los permisos de un usuario entre diferentes instancias, consulte si el artículo SQL Server: cómo copiar/replicar los permisos de un usuario puede ayudarte.
Usando el sistema SP, sp_helprotect
Mediante el uso del procedimiento almacenado. sp_helprotect, podemos enumerar fácilmente los permisos de un usuario u objeto determinado:
-- Verificando as permissões do usuário "Usuario_Teste"
EXEC Protheus_Producao.dbo.sp_helprotect
@username = 'Usuario_Teste'
Resultado Servidor SQL: permisos de usuario
-- Verificando as permissões da tabela "SA1010"
EXEC Protheus_Producao.dbo.sp_helprotect
@name = 'SA1010'
Resultado SQL Server - Permisos de usuario 2
Uso de consultas Transact SQL en vistas de catálogo
Una forma muy interesante de obtener y trabajar con esta información que vimos en la interfaz de SQL Server Management Studio es mediante consultas SQL y vistas de catálogo desde la base de datos de SQL Server. Estas consultas proporcionan más información que el sp del sistema sp_helprotect y al igual que usar el SP, podemos trabajar con los datos devueltos.
Ver código fuente
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
Devolución de consultas SQL Server: permisos de usuario a través de Transact SQL Query TSQL SQL
Usando un procedimiento almacenado personalizado
Con un poco más de trabajo y refinamiento, podemos utilizar un procedimiento almacenado para este propósito. Podemos utilizar los comentarios para generar un informe de acceso, registrar información para el historial, etc. Vea lo fácil que es:
Ver el código fuente del SP
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
Ejemplos de uso
-- 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
Ejemplo de devolución (primer ejemplo) SQL Server: permisos mediante consulta
Usando un procedimiento almacenado con código GRANT y REVOKE
Al igual que el procedimiento almacenado anterior, esta versión es un poco más “compacta” y aún genera el código T-SQL para otorgar este privilegio (en caso de que desee hacer una copia de seguridad de los permisos existentes) y también el código T-SQL para eliminar este permiso.
Ver código stpVerifica_Permissoes_Usuario
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
Ejemplos de uso:
-- 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
Resultado:
-- 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
Resultado:
-- 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
Resultado:
-- 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
Resultado:
Usando una consulta
Otra alternativa rápida y práctica para enumerar los permisos de los usuarios es utilizar una consulta con varios UNION para devolver permisos a nivel de rol, sistema y objeto.
SQL Server: tablas de objetos de roles del sistema de permisos de usuario de vista de lista
Ver código fuente de consulta
SELECT
DB_NAME(DB_ID()) AS DBNAME,
(CASE princ.type WHEN 'S' THEN princ.name WHEN 'U' THEN ulogin.name COLLATE Latin1_General_CI_AI END) AS UserName,
(CASE princ.type WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END) AS UserType,
princ.name AS DatabaseUserName,
NULL AS Role,
perm.[permission_name] AS PermissionType,
perm.state_desc AS PermissionState,
obj.type_desc AS ObjectType,
OBJECT_NAME(perm.major_id) AS ObjectName,
col.name AS ColumnName
FROM
sys.database_principals princ WITH(NOLOCK)
LEFT JOIN sys.login_token ulogin WITH(NOLOCK) ON princ.[sid] = ulogin.[sid]
LEFT JOIN sys.database_permissions perm WITH(NOLOCK) ON perm.grantee_principal_id = princ.principal_id
LEFT JOIN sys.columns col WITH(NOLOCK) ON col.[object_id] = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN sys.objects obj WITH(NOLOCK) ON perm.major_id = obj.[object_id]
WHERE
princ.type IN ('S', 'U')
UNION
SELECT
DB_NAME(DB_ID()) AS DBNAME,
(CASE memberprinc.type WHEN 'S' THEN memberprinc.name WHEN 'U' THEN ulogin.name COLLATE Latin1_General_CI_AI END) AS UserName,
(CASE memberprinc.type WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END) AS UserType,
memberprinc.name AS DatabaseUserName,
roleprinc.name AS [Role],
perm.[permission_name] AS PermissionType,
perm.state_desc AS PermissionState,
obj.type_desc AS ObjectType,
OBJECT_NAME(perm.major_id) AS ObjectName,
col.name AS ColumnName
FROM
sys.database_role_members members WITH(NOLOCK)
JOIN sys.database_principals roleprinc WITH(NOLOCK) ON roleprinc.principal_id = members.role_principal_id
JOIN sys.database_principals memberprinc WITH(NOLOCK) ON memberprinc.principal_id = members.member_principal_id
LEFT JOIN sys.login_token ulogin WITH(NOLOCK) ON memberprinc.[sid] = ulogin.[sid]
LEFT JOIN sys.database_permissions perm WITH(NOLOCK) ON perm.grantee_principal_id = roleprinc.principal_id
LEFT JOIN sys.columns col WITH(NOLOCK) ON col.[object_id] = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN sys.objects obj WITH(NOLOCK) ON perm.major_id = obj.[object_id]
UNION
SELECT
DB_NAME(DB_ID()) AS DBNAME,
'{All Users}' AS UserName,
'{All Users}' AS UserType,
'{All Users}' AS DatabaseUserName,
roleprinc.name AS [Role],
perm.[permission_name] AS PermissionType,
perm.state_desc AS PermissionState,
obj.type_desc AS ObjectType,
OBJECT_NAME(perm.major_id) AS ObjectName,
col.name AS ColumnName
FROM
sys.database_principals roleprinc WITH(NOLOCK)
LEFT JOIN sys.database_permissions perm WITH(NOLOCK) ON perm.grantee_principal_id = roleprinc.principal_id
LEFT JOIN sys.columns col WITH(NOLOCK) ON col.[object_id] = perm.major_id AND col.column_id = perm.minor_id
JOIN sys.objects obj WITH(NOLOCK) ON obj.[object_id] = perm.major_id
WHERE
roleprinc.[type] = 'R'
AND roleprinc.name = 'public'
AND obj.is_ms_shipped = 0
ORDER BY
princ.name,
OBJECT_NAME(perm.major_id),
col.name,
perm.[permission_name],
perm.state_desc,
obj.type_desc
Uso de la interfaz (GUI) de SQL Server Management Studio
Una forma muy sencilla y práctica de comprobar los permisos de los usuarios es utilizar la interfaz de SQL Server Management Studio para hacerlo. En mi opinión, el mayor problema de esta solución es que dependes de la herramienta y sabemos que un DBA no puede depender únicamente de la interfaz para su trabajo diario. Otro problema que le veo a esta solución es que no se puede trabajar con esta información y utilizarla para alguna rutina automática, por ejemplo.
Permisos a nivel de objeto SQL Server - Permisos de usuario - Interfaz GUI
En esta pantalla, podemos ver los esquemas que posee el usuario y los roles a nivel de base de datos de los que este usuario forma parte (Vea más sobre los roles de la base de datos aquí). SQL Server - Permisos de usuario - Membresía de rol de base de datos
En esta pantalla podemos enumerar los permisos otorgados sobre cada objeto en la base de datos y por lo tanto tenemos esta vista por usuario y objeto. SQL Server - Permisos de usuario - Interfaz GUI 2
Permisos a nivel de base de datos: SQL Server - Permisos de usuario - Interfaz GUI 3
En esta pantalla, podemos definir permisos a nivel de base de datos para un usuario determinado. Estos permisos se aplican a todos los objetos de esta base de datos que abarca este privilegio, además de los permisos de mantenimiento de la base de datos (Ej.: copia de seguridad, restauración, punto de control, etc.) SQL Server - Permisos de usuario - Interfaz GUI 4
Permisos a nivel de servidor: SQL Server - Permisos de usuario - Interfaz GUI 5
Permisos a nivel de servidor: roles de servidor
En esta pantalla, puede identificar de qué roles a nivel de servidor forma parte el usuario en cuestión. (vea más sobre los roles del lado del servidor aquí) SQL Server - Permisos de usuario - Interfaz GUI - Roles de servidor
Permisos a nivel de servidor – Mapeo de usuarios
En esta pantalla se puede identificar en qué bases de datos un determinado Login tiene un usuario creado y de qué roles a nivel de base de datos forma parte. SQL Server - Permisos de usuario - Interfaz GUI - Mapeo de usuarios
Permisos a nivel de servidor: asegurables
En este, podemos ver los permisos sobre las actividades de mantenimiento de la instancia, como Apagar, Crear cualquier inicio de sesión, Crear cualquier base de datos, etc. SQL Server - Permisos de usuario - Interfaz GUI - Securables explícitos
SQL Server - Permisos de usuario - Interfaz GUI - Securables efectivos
¡Eso es todo, amigos!
Espero que te haya gustado el post!
servidor sql vista de lista informe usuario rol del sistema objeto vista de tabla permisos vista de lista informe permisos accesos usuario usuarios
servidor sql vista de lista informe usuario rol del sistema objeto vista de tabla permisos vista de lista informe permisos accesos usuario usuarios
Dirceu Resende
Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…