SQL Server - determina de onde vem uma permissão

3

Eu tenho um banco de dados do SQL Server e uma conta do AD, que não deve ser capaz de acessar o banco de dados. O problema é que a conta tem acesso.

A conta é um membro de algumas dezenas de grupos, todos com diferentes permissões de acesso ao servidor e ao banco de dados.

Como posso saber exatamente quais membros do grupo oferecem acesso à conta?

    
por Test_me 12.04.2017 / 19:54

1 resposta

1

Eu gostaria de fornecer alguns scripts que podem ser úteis para essa tarefa. Estes devem ser métodos TSQL puros, você pode executar o diretório do (s) DB (s) específico (s) em questão no SSMS.

Script 1

(não tem referência à fonte ou a esta)

CREATE VIEW vwObjectPermissions
AS
SELECT schema_name(o.schema_id) AS [Schema_Name]
    ,o.NAME AS [object_name]
    ,u.NAME AS [principal_name]
    ,u.type_desc AS [principal_type]
    ,r.minor_id
    ,r.permission_name
    ,r.state_desc
    ,o.schema_id
    ,o.principal_id AS [alt_owner]
    ,o.type_desc
FROM sys.database_permissions r
LEFT JOIN sys.database_Principals u ON r.grantee_principal_id = u.principal_id
LEFT JOIN sys.all_objects o ON o.object_id = r.major_id
WHERE class_desc NOT IN ('database')
GO

--1. Check if Public or guest is granted any permission on an object (database role and server role)
SELECT *
FROM vwObjectPermissions
WHERE principal_name IN ('Public','Guest')

--2. Check if any user is granted permissions on an object rather than roles.
SELECT *
FROM vwObjectPermissions
WHERE principal_type NOT LIKE '%ROLE%'

--3. Check if a user has "with grant" previliges on an object
SELECT *
FROM vwObjectPermissions
WHERE state_desc = 'WITH GRANT' --check the spelling on this one

--4. Check who has access to extended stored procedures (which I get from select name from sysobjects where xtype='X')
SELECT *
FROM vwObjectPermissions
WHERE type_desc LIKE '%X%Proc%'
GO

DROP VIEW vwObjectPermissions;

Script 2

fonte

set nocount on
declare @permission table (
Database_Name sysname,
User_Role_Name sysname,
Account_Type nvarchar(60),
Action_Type nvarchar(128),
Permission nvarchar(60),
ObjectName sysname null,
Object_Type nvarchar(60)
)
declare @dbs table (dbname sysname)
declare @Next sysname
insert into @dbs
select name from sys.databases order by name
select top 1 @Next = dbname from @dbs
while (@@rowcount<>0)
begin
insert into @permission
exec('use [' + @Next + ']
declare @objects table (obj_id int, obj_type char(2))
insert into @objects
select id, xtype from master.sys.sysobjects
insert into @objects
select object_id, type from sys.objects

SELECT ''' + @Next + ''', a.name as ''User or Role Name'', a.type_desc as ''Account Type'',
d.permission_name as ''Type of Permission'', d.state_desc as ''State of Permission'',
OBJECT_SCHEMA_NAME(d.major_id) + ''.'' + object_name(d.major_id) as ''Object Name'',
case e.obj_type
when ''AF'' then ''Aggregate function (CLR)''
when ''C'' then ''CHECK constraint''
when ''D'' then ''DEFAULT (constraint or stand-alone)''
when ''F'' then ''FOREIGN KEY constraint''
when ''PK'' then ''PRIMARY KEY constraint''
when ''P'' then ''SQL stored procedure''
when ''PC'' then ''Assembly (CLR) stored procedure''
when ''FN'' then ''SQL scalar function''
when ''FS'' then ''Assembly (CLR) scalar function''
when ''FT'' then ''Assembly (CLR) table-valued function''
when ''R'' then ''Rule (old-style, stand-alone)''
when ''RF'' then ''Replication-filter-procedure''
when ''S'' then ''System base table''
when ''SN'' then ''Synonym''
when ''SQ'' then ''Service queue''
when ''TA'' then ''Assembly (CLR) DML trigger''
when ''TR'' then ''SQL DML trigger''
when ''IF'' then ''SQL inline table-valued function''
when ''TF'' then ''SQL table-valued-function''
when ''U'' then ''Table (user-defined)''
when ''UQ'' then ''UNIQUE constraint''
when ''V'' then ''View''
when ''X'' then ''Extended stored procedure''
when ''IT'' then ''Internal table''
end as ''Object Type''
FROM [' + @Next + '].sys.database_principals a 
left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id
left join @objects e on d.major_id = e.obj_id
order by a.name, d.class_desc')
delete @dbs where dbname = @Next
select top 1 @Next = dbname from @dbs
end
set nocount off
select * from @permission

Mais recursos

Permissões de nível de banco de dados de script v3

    
por 12.04.2017 / 20:25