Yetki Sorgulama Scriptleri - 2 - Tüm Kullanıcıların Sunucu ve Veritabanı Rollerini Sorgulamak
Bir önceki makalemde db_owner listesini elde etmiştik. Bu makalemde vereceğim sorgu ise daha detaylı bir liste çıkartarak sunucu üzerinde bulunan tüm kullanıcıların sunucu ve veritabanı seviyesindeki rollerini sorgulamak için kullanılabilir.
Sorguya geçmeden önce sunucu ve veritabanı seviyesindeki roller derken neyi kastediyoruz buna bir bakalım.
SQL Server'da sunucu seviyesinde verilebilecek roller aşağıdaki gibidir.
ServerRole Description
======== ============
sysadmin System Administrators
securityadmin Security Administrators
serveradmin Server Administrators
setupadmin Setup Administrators
processadmin Process Administrators
diskadmin Disk Administrators
dbcreator Database Creators
bulkadmin Bulk Insert Administrators
Sunucu seviyesindeki roller 2011 Denali sürümünden önce sabitken 2011 Denali ile beraber kullanıcı tanımlı sunucu seviyesinde rol tanımlanması mümkün hala gelecek.
Veritabanı seviyesindeki rolleri ise aşağıdaki resimde kontrol edebiliriz. Bu rollerden en bilindikleri db_owner, db_datareader, db_datawriter ve db_ddladmin rolleridir.
Bu yetkilerin ötesinde kullanıcılara obje bazında yetki verilebilmektedir. Örneğin x bir kullanıcıya y tablosu üzerinde spesifik olarak okuma ve yazma yetkisi verilebilir. Bu yetkiler aşağıda verilen sorgunun kapsamı dışındadır. Obje bazında verilen yetkileri kontrol etmek için ilerleyen günlerde başka bir makale yayınlayacağım.
Sunucu ve veritabanı seviyesindeki rolleri sorgulamak için aşağıdaki sorguyu kullanabiliriz.
create table #ServerLogins (name sysname, is_disabled int, type_desc varchar(60), create_date datetime, modify_date datetime, server_roles varchar(500), db_roles varchar(max))
create table #DBLevelPermissions (DBName sysName, DBRole sysname, MemberName sysName)
declare @str1 varchar(max)=''
,@str2 varchar(max)=''
,@name sysname
,@is_disabled int
,@type_desc varchar(60)
,@create_date datetime
,@modify_date datetime
select lgn.name as Name, SUSER_NAME(rm.role_principal_id) as RoleName
into #ServerLevelPermissions
FROM SYS.SERVER_ROLE_MEMBERS RM, SYS.server_principals LGN
WHERE RM.role_principal_id >=3 AND RM.role_principal_id <=10 AND
RM.member_principal_id = LGN.principal_id
select name,is_disabled,type_desc,create_date,modify_date
into #ServerPrincipals
from sys.server_principals
where type not in ('C','R')
and name not in ('sa','##MS_PolicyTsqlExecutionLogin##','##MS_PolicyEventProcessingLogin##','NT AUTHORITY\SYSTEM'
,'NT AUTHORITY\NETWORK SERVICE','NT SERVICE\MSSQLSERVER','NT SERVICE\SQLSERVERAGENT')
order by name
exec sp_msforeachdb 'use ?;
insert #DBLevelPermissions
select ''?'',g.name as DBRole, u.name as MemberName
from sys.database_principals u, sys.database_principals g, sys.database_role_members m
where g.principal_id = m.role_principal_id
and u.principal_id = m.member_principal_id
order by 1, 2'
declare CursorX cursor for
select name,is_disabled,type_desc,create_date,modify_date from #ServerPrincipals
open Cursorx
fetch from Cursorx into @name,@is_disabled,@type_desc,@create_date,@modify_date
while @@FETCH_STATUS=0
begin
set @str1=''
select @str1+=RoleName+',' from #ServerLevelPermissions where name=@name
if @str1<>''
select @str1=SUBSTRING(@str1,0,len(@str1))
set @str2=''
select @str2+=DBName+'('+DBRole+')'+',' from #DBLevelPermissions where MemberName=@name
if @str2<>''
select @str2=SUBSTRING(@str2,0,len(@str2))
insert #ServerLogins
select @name,@is_disabled,@type_desc,@create_date,@modify_date,@str1,@str2
fetch next from Cursorx into @name,@is_disabled,@type_desc,@create_date,@modify_date
end
close CursorX
deallocate CursorX
select * from #ServerLogins order by name
drop table #ServerPrincipals
drop table #ServerLevelPermissions
drop table #ServerLogins
drop table #DBLevelPermissions
Örnek rapor çıktısı şu şekilde olacaktır:
