SQL Server’da Tüm Yetkilendirmeleri Listelemek

Maksimum güvenlik gereği minimum yetki prensibi gereği düzenli olarak sql server üzerinde hangi kullanıcının ne yetkisi olduğu kontrol etmeli ve gereksiz yetkilendirilmiş kullanıcıların yetkilerini kaldırmalıyız. Bu makalede,

Server Seviyesinde Yetkilendirilmiş Kullanıcılar,

Veritabanı Seviyesinde Yetkilendirilmiş Kullanıcılar,

Şema Seviyesinde Yetkilendirilmiş Kullanıcılar ve

Nesne Seviyesinde Yetkilendirilmiş Kullanıcılar

İle ilgili script’leri paylaşacağım.

Server Seviyesinde Yetkilendirilmiş Kullanıcıları Görmek İçin:

select sp.name, sp2.name
from sys.server_role_members srm
       join sys.server_principals sp on sp.principal_id=srm.role_principal_id
       join sys.server_principals sp2 on sp2.principal_id=srm.member_principal_id

Veritabanı Seviyesinde Yetkilendirilmiş Kullanıcıları Görmek İçin:

Eğer always on kullanıyorsanız instance üzerinde primary olan ve always on’a dahil olmayan tüm veritabanları için sonuç getirir.

DECLARE @name  NVARCHAR(MAX),@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX);

/*Cursor'a bir isim veriyoruz*/

DECLARE Crs CURSOR
FOR

/*Cursor'ın dolaşacağı kayıt kümesini belirten select cümleceği*/

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
SELECT  DB_NAME(dr_state.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id
and dr_state.replica_id = ar_state.replica_id
where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1
)
OPEN Crs
FETCH NEXT FROM Crs INTO @Name

/*WHILE @@FETCH_STATUS=0 Cursorda dolaşacak kayıt kalmayana kadar bir sonraki kayda geçerek cursor'ı döndürmeye devam et demek*/

WHILE @@FETCH_STATUS =0
BEGIN   
Select @Sql = 'Use ' + @name + ';
SELECT '''+@name+''' AS DBNAME,DP.name AS RoleName
      ,DP2.name AS MemberName
      ,DP2.type_desc AS MemberType
      ,DP2.default_schema_name AS DefaultSchema
      ,SP.name AS ServerLogin
FROM sys.database_role_members AS DRM
     INNER JOIN sys.database_principals AS DP ON DRM.role_principal_id = DP.principal_id
     INNER JOIN sys.database_principals AS DP2  ON DRM.member_principal_id = DP2.principal_id
     INNER JOIN sys.server_principals AS SP
         ON DP2.[sid] = SP.[sid]
       where SP.is_disabled=0
ORDER BY RoleName,MemberName;
'
Exec sp_executesql @Sql 

 FETCH NEXT FROM Crs INTO @Name
 END     
/*CLOSE ve DEALLOCATE komutlatı ile Cursor'ı kapatıyoruz*/
CLOSE Crs
DEALLOCATE Crs

Şema Seviyesinde Yetkilendirilmiş Kullanıcıları Görmek İçin:

Eğer always on kullanıyorsanız instance üzerinde primary olan ve always on’a dahil olmayan tüm veritabanları için sonuç getirir.

DECLARE @name  NVARCHAR(MAX),@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX);


/*Cursor'a bir isim veriyoruz*/

DECLARE Crs CURSOR
FOR

/*Cursor'ın dolaşacağı kayıt kümesini belirten select cümleceği*/

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
SELECT  DB_NAME(dr_state.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id
and dr_state.replica_id = ar_state.replica_id
where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1
)
OPEN Crs
FETCH NEXT FROM Crs INTO @Name

/*WHILE @@FETCH_STATUS=0 Cursorda dolaşacak kayıt kalmayana kadar bir sonraki kayda geçerek cursor'ı döndürmeye devam et demek*/

WHILE @@FETCH_STATUS =0
BEGIN   
Select @Sql = 'Use ' + @name + ';
if exists(select 1 FROM sys.database_permissions
 WHERE class_desc = ''SCHEMA'')
BEGIN
SELECT '''+@name+''' AS [Veritabanı İsmi],SCHEMA_NAME(major_id) [Şema İsmi]
     , USER_NAME(grantee_principal_id) [Login İsmi]
     , permission_name [Yetki]
 FROM sys.database_permissions
 WHERE class_desc = ''SCHEMA''
 ORDER BY major_id, grantee_principal_id, permission_name
 END
'

Exec sp_executesql @Sql 
 FETCH NEXT FROM Crs INTO @Name
 END     

/*CLOSE ve DEALLOCATE komutlatı ile Cursor'ı kapatıyoruz*/
CLOSE Crs
DEALLOCATE Crs

Nesne Seviyesinde Yetkilendirilmiş Kullanıcıları Görmek İçin:

Aşağıdaki script ile tek veritabanı için bu yetkileri görebilirsiniz.

SELECT permission_name AS Yetki, type_desc [Nesne Tipi], U.name [Login İsmi], OBJECT_NAME(major_id) [Nesne İsmi]
from sys.database_permissions dp
JOIN sys.tables tbl ON dp.major_id = tbl.object_id
JOIN sysusers u ON u.uid = dp.grantee_principal_id

Eğer always on kullanıyorsanız instance üzerinde primary olan ve always on’a dahil olmayan tüm veritabanları için aşağıdaki script’i kullanabilirsiniz.

DECLARE @name  NVARCHAR(MAX),@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX);

/*Cursor'a bir isim veriyoruz*/

DECLARE Crs CURSOR

FOR

/*Cursor'ın dolaşacağı kayıt kümesini belirten select cümleceği*/

SELECT name FROM sys.sysdatabases where dbid>4 and name not in(
SELECT  DB_NAME(dr_state.database_id) as name
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id
and dr_state.replica_id = ar_state.replica_id
where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1
)
OPEN Crs
FETCH NEXT FROM Crs INTO @Name

/*WHILE @@FETCH_STATUS=0 Cursorda dolaşacak kayıt kalmayana kadar bir sonraki kayda geçerek cursor'ı döndürmeye devam et demek*/

WHILE @@FETCH_STATUS =0
BEGIN   

Select @Sql = 'Use ' + @name + ';
if exists(SELECT 1 from sys.database_permissions dp
JOIN sys.tables tbl ON dp.major_id = tbl.object_id
JOIN sysusers u ON u.uid = dp.grantee_principal_id)
BEGIN
SELECT '''+@name+''',permission_name AS Yetki, type_desc [Nesne Tipi], U.name [Login İsmi], OBJECT_NAME(major_id) [Nesne İsmi]
from sys.database_permissions dp
JOIN sys.tables tbl ON dp.major_id = tbl.object_id
JOIN sysusers u ON u.uid = dp.grantee_principal_id
END
'

Exec sp_executesql @Sql 

 FETCH NEXT FROM Crs INTO @Name
 END     

/*CLOSE ve DEALLOCATE komutlatı ile Cursor'ı kapatıyoruz*/

CLOSE Crs
DEALLOCATE Crs