У меня есть база данных SQL Server и учетная запись AD, которая не должна иметь доступ к БД. Проблема в том, что на самом деле учетная запись имеет доступ.
Учетная запись является членом нескольких десятков групп с разными правами доступа к серверу и БД.
Как я могу точно узнать, какое членство в группе дает доступ к учетной записи?
Я хотел предоставить вам пару сценариев, которые могут быть полезны для этой задачи. Это должны быть чистые методы TSQL, которые вы можете запускать каталог из конкретных БД в SSMS.
(Нет ссылки на источник или этот)
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;
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