Veritabanı Yöneticilerinin Veriye Erişimini Kısıtlamak(Control Server ve sysadmin Farkları)

Control Server Yetkisi SQL Server 2005 ile beraber hayatımıza girdi. Bu yetki sysadmin’in bir alternatifi olarak karşımıza çıktı. Sysadmin ise bildiğiniz gibi sql server’da herşeyi yapma hakkına sahip olan süper bir kullanıcı.

 

Control server ve sysadmin’in farklarını benim gördüğüm kadarıyla aşağıdaki tabloda bulabilirsiniz. Bunlar ilk anda gördüğüm farklar. Eminim başka farkları da vardır. Ama bu kadar fark durmam için yetti diyebilirim.

 

 

 

 

Gördüğünüz gibi Control Server yetkisinin en önemli özelliğinin sysadmin hakkına sahip olmasa bile sysadmin hakkına sahip kullanıcıları taklit(impersonate) edebiliyor.

 

Control Server yetkisine sahip bir kullanıcının server seviyesindeki haklarını aşağıda görebilirsiniz.

 

 

CONNECT SQL

SHUTDOWN

CREATE ENDPOINT

CREATE ANY DATABASE

CREATE AVAILABILITY GROUP

ALTER ANY LOGIN

ALTER ANY CREDENTIAL

ALTER ANY ENDPOINT

ALTER ANY LINKED SERVER

ALTER ANY EXTERNAL DATA SOURCE

ALTER ANY EXTERNAL FILE FORMAT

ALTER ANY CONNECTION

ALTER ANY DATABASE

ALTER RESOURCES

ALTER SETTINGS

ALTER TRACE

ALTER ANY AVAILABILITY GROUP

ADMINISTER BULK OPERATIONS

AUTHENTICATE SERVER

EXTERNAL ACCESS ASSEMBLY

VIEW ANY DATABASE

VIEW ANY DEFINITION

VIEW SERVER STATE

CREATE DDL EVENT NOTIFICATION

CREATE TRACE EVENT NOTIFICATION

ALTER ANY EVENT NOTIFICATION

ALTER SERVER STATE

UNSAFE ASSEMBLY

ALTER ANY SERVER AUDIT

CREATE SERVER ROLE

ALTER ANY SERVER ROLE

ALTER ANY EVENT SESSION

CONNECT ANY DATABASE

IMPERSONATE ANY LOGIN

CONTROL SERVER

 

 

Aşağıdaki sorgu ile bağlı olduğunuz kullanıcının instance üzerinde hangi yetkilerinin olduğunu görebilirsiniz.

 

SELECT entity_name, permission_name

FROM sys.fn_my_permissions(NULL, NULL)

 

 

Konunun daha net anlaşılması için öncelikle SQL Server 2014 ile birlikte gelen aşağıdaki yetkileri anlamanız gerekmektedir.

 

CONNECT ANY DATABASE: Bu yetkiyi bir login’e verirseniz Instance üzerindeki veritabanlarına bağlanabilir fakat veritabanları üzerinde hiçbir yetkisi olmaz. Yani tablolara select bile çekemez.

 

IMPERSONATE ANY LOGIN: Bu yetkiyi bir login’e verirseniz Instance üzerindeki bir sysadmin’i taklit edebilir. Ya da tam tersi CONTROL Server yetkisi olan birinin sysadmin’i taklit etmesini engellemek için deny edebilirsiniz. 🙂

 

SELECT ALL USER SECURABLES: Bu yetkiyi bir login’e verirseniz bağlanabildiği her veritabanındaki verilere erişebilir. CONNECT ANY DATABASE yetkisi ile beraber verirseniz bir anlam ifade edecektir.

 

Yukarda bahsi geçen tüm yetkileri içeren bir senaryo düşünelim.

 

Kurumumuza yeni gelmiş bir dba olsun. Ve bu DBA’in bir çok işi yapmasını ama verileri görememesini sağlamak gibi bir amacımız olsun.

 

Öncelikle bu login’e CONTROL Server yetkisi verelim.

 

Sonrasında ise verileri görememesi için SELECT ALL USER SECURABLES yetkisini DENY edelim.

 

Ardından Instance üzerindeki bir sysadmin’i taklit edememesi için de IMPERSONATE ANY LOGIN yetkisini DENY edelim.

 

Böylelikle bu login yukarda CONTROL SERVER’ın yetkileri kısmında gördüğünüz herşeyi yapabilecek, fakat verilere erişemeyecektir. CONTROL SERVER yetkisi ile yapılamayacak olan işlemlerde sysdamin haklarına sahip kullanıcı nezdinde gerçekleştirilebilir.

 

Yukarda anlattığım senaryoyu gerçek bir örnek yaparak uygulayalım.

 

Öncelikle sysadmin hakkına sahip sysadminLogin isminde bir kullanıcı oluşturuyoruz. “Login oluşturmak ve yetkilendirmek” isimli makalede bu işlemi nasıl yapacağınızı görebilirsiniz.

 

Daha sonra server ya da veritabanı seviyesinde hiç yetkisi olmayan controlserverLogin isminde başka bir login oluşturalım.

 

Daha sonra aşağıdaki gibi Instance üzerinde sağ tıklayarak properties diyoruz.

 

 

 

Karşımıza gelen ekranda Permission sekmesine gelerek Search diyoruz.

 

 

Karşımıza gelen ekranda Select these object roles kısmında Logins, Server Roles seçili iken Browse diyoruz.

 

 

Karşımıza gelen ekranda controlserverLogin’i bularak yanındaki kutucuğu tıklıyoruz ve ok diyoruz.

 

 

Karşımıza tekrar bir önceki ekran geliyor. Bu ekranda aşağıdaki gibi controlserverLogin’i seçiyoruz ve controlserverLogin seçili iken alt taraftaki Control server’a Grant veriyoruz.(Grant’ın altındaki kutucuğu tıklıyoruz) Ok diyerek işlemi tamamlıyoruz.

 

 

Yukardaki gibi controlserverLogin’e instance seviyesinde control server yetkisi verdikten sonra bu login ile instance’a bağlanarak yapabildiklerini test ediyoruz.

 

İlk olarak gördüğünüz gibi SQL Server Agent’ı göremiyoruz.

 

 

Server Object kısmından new server login dediğimizde aşağıdaki gibi bir hata alıyoruz. Fakat mevcut bir linked server’ı alter edebiliyoruz.

 

A required operation could not be completed. You must be a member of the sysadmin role to perform this operation.

 

 

Peki ControlServerLogin ile bir veritabanında yetkisi olan bir kullanıcı oluşturabiliyor muyuz?

 

Aşağıdaki script’te gördüğünüz gibi bir login oluşturup veritabanında alter yetkisi veren script’i herhangi bir engel karşılaşmadan verebiliyoruz.

 

USE [master]
GO
CREATE LOGIN [controlservertest] WITH PASSWORD=N'xx', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2014Yeni]
GO
CREATE USER [controlservertest] FOR LOGIN [controlservertest]
GO
GRANT ALTER TO [controlservertest]
GO

 

 

 

Eğer login’e aşağıdaki script yardımıyla db_owner hakkı vermek istersek aşağıdaki gibi hata alıyoruz.

 

USE [AdventureWorks2014Yeni]
GO
ALTER ROLE [db_owner] ADD MEMBER [controlservertest]
GO

 

 

Msg 15151, Level 16, State 1, Line 4

Cannot alter the role ‘db_owner’, because it does not exist or you do not have permission.

 

 

ControlServerLogin ile aşağıdaki gibi herhangi bir veritabanına select çekebiliyor yada veritabanı oluşturup silebiliyoruz.

 

 

Kısaca birkaç özellik göstermek istedim.  Makalenin başında yapabileceklerini listelemiştim. Burada hepsine tek tek değinmemiz biraz zor olacaktır. İlgisi olan arkadaşlar tek tek deneyebilirler.

 

Konumuza geri dönecek olursak amacımız Control Server yetkisine sahip birinin verileri görmesini engellemekti. Bu yüzden Instance seviyesinde  SELECT ALL USER SECURABLES hakkını DENY etmemiz gerekiyor.

 

Instance üzerinde yeniden sağ tıklayarak properties diyoruz ve permission sekmesine gelerek Login and roles kısmından controlserverLogin’i seçiyoruz(Burada yoksa yukarda anlattığım şekilde search diyebilirsiniz). Ve aşağıdaki permission kısmından Select All User Securables’ı seçip DENY diyoruz.

 

 

Artık controlServerLogin’i ile tablolardan birine select çektiğinizde aşağıdaki gibi hata alırsınız.

 

Msg 229, Level 14, State 5, Line 2

The SELECT permission was denied on the object ‘DatabaseLog’, database ‘AdventureWorks2014Yeni’, schema ‘dbo’.

 

 

Son aşama olarak controlserverLogin’in sysadminLogin gibi nasıl davranabileceğini ve bunu nasıl engelleyeceğimizi anlatıp makaleyi noktalayacağım.

 

Biraz önce tabloya select çektiğimizde hata almıştık. controlserverLogin’e sysadminLogin’i taklit ettirek aynı select sorgusunu yeniden çalıştırtalım.

 

Aşağıdaki script ile bunu yapabilirsiniz.

EXECUTE AS LOGIN = 'sysadminLogin'
GO
SELECT *
FROM [AdventureWorks2014Yeni].[dbo].[DatabaseLog]

 

 

Gördüğünüz gibi normalde tablolara select çekemezken sysadminLogin’i taklit ederek controlserverLogin ile bunu başarabildik.

 

Stored Procedure’lerde de taklit etme işlemini(impersonate) aşağıdaki şekilde yapabiliriz.

 

CREATE PROCEDURE [dbo].[procedureismi]
WITH EXECUTE AS 'sysadminLogin'
AS

.
.

 

 

Peki CONTROL SERVER yetkisinin taklit etme(impersonate) yeteneğini nasıl engelleriz?

 

Makalenin başında da bahsettiğim gibi instance seviyesinde IMPERSONATE ANY LOGIN yetkisini DENY ederek bunu başarabiliriz. Bir önceki adımda Select All User Securables yetkisini DENY ettiğimiz şekilde Impersonate Any Login’i de DENY etmelisiniz.

Login’lere View_Server_State Yetkisi Vermeden dmw ve dmf’lere Kısıtlı Bir Şekilde Eriştirmek

“Admin olmayan Loginlerin dmw’leri ve dmf’leri okuyabilmesi için gerekli yetkiyi vermek” isimli makalede Login’lerin dmw ve dmf’eri okuyabilmesi için gerekli yetkinin nasıl vereceğini anlattım.

 

Fakat bahse konu makalede verilen yetki ile loginler instance’taki tüm veritabanlarına gelen sorguları görebiliyorlar. Eğer instance üzerinde birden fazla kişiye ait veritabanı varsa bu bir güvenlik açığıdır. Bu makalede her uygulamacıya kendi veritabanına gelen sorguları görecek kadar yetkiyi nasıl vereceğimizi öğreneceğiz.

 

Öncelikle Test isminde bir veritabanı ve bu veritabanında yetkisi olan TestLogin isminde bir login oluşturalım. İlgili adımları nasıl yapacağınızı bilmiyorsanız aşağıdaki makaleler yardımcı olacaktır.

“Veritabanı Oluşturmak Deyip Geçmeyin!“,

“Login oluşturmak ve yetkilendirmek“

 

Öncelikle instance üzerinde TRUSTWORTH özelliği aktif edilmiş bir veritabanına ihtiyacımız var. Veritabanını oluşturduktan sonra kendi veritabanında TRUSTWORTHY özelliğini aktif hale getirebilirsiniz fakat bunu tavsiye etmiyorum. Çünkü TRUSTWORTHY özelliği aktif edilmiş bir veritabanı SQL Server için güvenilir olarak set edilmiş olur.

 

Bu özellik aktif iken veritabanı içersinden EXECUTE AS komutu ile sysadmin hakkında sahip bir kullanıcı ile işlem yapılabilir. Yani uygulama veritabanında db_owner hakkına sahip birine, aslında sysadmin olmadığı halde sysadmin yetkisi vermiş olursunuz.

 

Ayrıca TRUSTWORTH özelliği aktif edilmiş bir veritabanında .NET ile derlenmiş kod’lar çalıştırılabilir. Buda ayrı bir güvenlik açığıdır.

 

Bu sebeplerden dolayı bu işlemler için yeni ve kimsenin yetkisinin olmadığı bir veritabanı oluşturalım. Veritabanı adına da  TRUSTDB diyelim(Siz başka bir isim verebilirsiniz).

 

 

Ayrıca instance üzerinde sysadminUser(siz başka bir isim verebilirsiniz) isminde sysadmin hakkına sahip bir sql login oluşturalım.

 

Bu veritabanında TRUSTWORTHY özelliğini aşağıdaki script yardımıyla aktif hale getirelim.

ALTER DATABASE TRUSTDB SET TRUSTWORTHY ON;

 

Daha sonra aşağıdaki script yardımıyla da ilgili kullanıcıya sadece kendi veritabanına gelen sorguları görebileceği bir stored procedure oluşturalım. sysadminUser yerine sizin instance’ınız üzerinde sysadmin hakkına sahip bir login ismi, Test yerine de ilgili login’e ait veritabanı ismini yazmalısınız. İlgili login burada yazacağınız veritabanına gelen sorguları görebilecek.

USE [TRUSTDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[X_Veritabani_Icin_Sorgu_Performansi]
WITH EXECUTE AS 'sysadminUser'
AS
select r.total_elapsed_time / 1000.0 as total_elapsed_s,percent_complete,
r.blocking_session_id,r.last_wait_type,s.login_name,
'thisrequests_sessionid=' + cast(r.session_id as varchar) SessionID,
DB_NAME(r.database_id) as DatabaseName,command
,SUBSTRING(t.text, (r.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(t.text)
        ELSE r.statement_end_offset END
            - r.statement_start_offset)/2) + 1) AS statement_text
,r.status,blocking_session_id,wait_time
,wait_type,wait_resource,text,start_time,r.percent_complete,s.program_name
,r.last_wait_type,s.host_name,r.granted_query_memory * 8 / 1024 as memory_mb
from sys.dm_exec_requests r
inner join sys.dm_exec_sessions s on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) t
where r.session_id <> @@SPID
and (r.database_id=DB_ID('Test'))
order by start_time asc

 

Daha sonra yetki vermek istediğiniz kullanıcıya oluşturduğunuz bu stored procedure üzerinde aşağıdaki gibi execute hakkı vermeniz gerekiyor.

 

Öncelikle TestLogin’e TRUSTDB üzerinde sadece public hakkı veriyoruz.

 

Daha sonra TRUSTDB’ye sağ tıklayarak aşağıdaki gibi ilgili stored procedure üzerinde Properties diyoruz.

 

 

Açılan ekranda Permission tab’ına gelerek Search diyoruz. Açılan ekranda Browse diyerek aşağıdaki gibi TestLogin’i seçiyoruz ve ok diyoruz.

 

 

Alt taraftaki ekranda da ok diyoruz ve karşımıza gelen ekranda TestLogin seçili iken aşağıdaki gibi Execute hakkı veriyoruz.

 

 

Artık TestLogin’e sadece Test veritabanındaki anlık sorguları getirecek stored procedure üzerinde execute yetkisi vermiş olduk.

 

Bir sonraki adımda test etmek için TestLogin ile login olup aşağıdaki gibi stored procedure’ü çalıştırabilirsiniz.

 

USE [TRUSTDB]
GO
EXEC [dbo].[X_Veritabani_Icin_Sorgu_Performansi]

 

Bu örnekleri çoğaltabilirsiniz. Örneğin ben bu makalede anlattığım şekilde bir stored procedure oluşturup ilgili kişilere bu stored procedure’ü execute etme yetkisi vermiştim.

 

Onlar da bu sp’nin sonucunda gelmeyen bir sorgunun diğer sorguları lock’ladığını ve locklayan sorgunun spid’sini görebildiklerini fakat sorguyu göremediklerini söylemişlerdi.

 

Bende aşağıdaki gibi bir stored procedure oluşturdum.

 

 

CREATE PROCEDURE [dbo].[AssociatedQueryForVeritabaniAdiniz]
@AssociatedQueryId int
WITH EXECUTE AS 'sysadminUser'
AS
DECLARE @sqlbinary varbinary(1024)
select @sqlbinary=sql_handle from sys.sysprocesses where dbid=DB_ID('VeritabaniAdiniz') AND spid=@AssociatedQueryId

select * from sys.dm_exec_sql_text(@sqlbinary)

 

Bu sp’yi de aşağıdaki şekilde çalıştırmaları gerekiyor.

 

Aşağıdaki sorguda 196 numaraları spid’nin hangi sorguya ait olduğu bilgisi öğrenilmek isteniyor.

 

Execute [AssociatedQueryForVeritabaniAdiniz] 196

 

Loginlerin Owner Oldukları Veritabanında Backup Almasını Engellemek

Loginleri veritabanında db_owner olarak yetkilendirmemeliyiz. Bu güvenlik zaafiyetine neden olacaktır. Örneğin makalemize konu olan riskten bahsedelim. Uygulamacı owner’ı olduğu veritabanının backup’ını almak isterse, backup’ı alacağı disk olarak karşısına birkaç drive ismi çıkacaktır. Bu drive isimleri aslında sql server’ın bulunduğu sunucu üzerindeki disklere aittir. Uygulamacının bu drive’ları kendi diskleri zannederek backup’ı başlattığını düşünün. Eğer backup’ı aldığı diski bu şekilde doldurursa ve bu disk üzerinde veritabanı dosyaları varsa veritabanı çalışamaz hale gelecektir. Eğer işletim sisteminin olduğu diske almaya çalışırsa bu sefer işletim sistemini bile çökertebilir.

 

Bu yüzden veritabanında uygulamacıları owner yapmak yerine bir veritabanı rolü oluşturarak ihtiyacı kadar yetki verebilirsiniz. Veritabanı seviyesinde rol oluşturmak için “Veritabanı Seviyesinde Rol Oluşturmak” isimli makaleyi, tüm db_owner yetkisi olan loginleri görmek için de “SQL Server’da Tüm Yetkilendirmeleri Listelemek” isimli makaleyi okuyabilirsiniz.

Db_owner hakkında sahip kullanıcıların backup almasını engellemek yerine yukarda bahsettiğim işlemleri yapmanızı tavsiye ederim. Ama yine de db_owner olsun backup alamasın diyorsanız da aşağıdaki adımları takip edebilirsiniz.

 

Öncelikle backup alınmasını engellemek istediğiniz veritabanı sağ tıklayıp özellikler demelisiniz. Karşınız aşağıdaki gibi bir ekran gelecektir. Bu ekrandan Permissions sekmesine tıklayarak backup almasını engellemek istediğiniz db_owner hakkında sahip olan kullanıcıya tıklayarak aşağıda tarafta gördüğünüz Backup database kısmından Deny’ı seçmeli ve ok’e tıklamalısınız.

 

Deny ettikten sonra bu kullanıcıyla backup almak istendiğinde aşağıdaki gibi hata alacaktır.

 

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

 

Column Master Key Rotasyon İşlemi(Always Encrypted)

Bazı durumlarda Always Encrypted ile şifrelediğiniz bir veritabanı için daha önce oluşturulan Column Master Key’i değiştirmeniz gerekebilir. Bu makalede bu değiştirme işleminin nasıl yapılacağını inceleyeceğiz.

 

Always Encrypted ile ilgili detaylı bilgi almak için “Always Encrypted Nedir ve Nasıl Kullanılır?” isimli makalemi okumanızı tavsiye ederim.

 

Bu makaleyi direk bir örnek üzerinden anlatacağım.

 

Daha önce test isminde oluşturulmuş bir column master key’imizin olduğunu varsayıyorum. Aşağıdaki gibi öncelikle yeni bir column master key oluşturuyoruz.

 

 

Karşımıza gelen ekranda column master key’imize bir isim vererek nerede saklanacağını belirliyoruz ve ok diyoruz. Biz örneğimizde “masterkeyrotationornek” ismini verdik.

 

 

Daha sonra önceki column master key’e sağ tıklayarak Rotate diyoruz.

 

Karşımıza gelen ekranda Target kısmına yeni column master key’imiz olan masterkeyrotationornek’i seçiyoruz ve ok diyoruz.

 

Daha sonra aşağıdaki gibi eski master key üzerinde sağ tıklayarak cleanup diyoruz.

 

Karşımıza aşağıdaki gibi bir ekran geliyor. Ok diyerek işlemi tamamlıyoruz.

 

Son olarak da eski master key’imizi siliyoruz.