MSSQL Server Login-User İlişkisi ve Yetkileri

Login dediğimiz kavram sql server’da instance bazlı bir kullanıcı olmakla birlikte user ise sadece database bazlı olduğu için  database üzerinde işlem yapabilen bir kullanıcıdır. Kısacası loginler tüm instance ve databaseler üzerinde yetkiliyken user’lar ise ilgili olduğu database üzerinde yetkilidir. Bir Login, bir veya birden fazla veritabanında User olabilir.

User’lar Instance üzerinde yetki sahibi olamazlar. Sql server da herhangi bir login oluşturup bu logine veritabanı bazında yetki verdiğimizde bu login ile aynı SID değerine sahip  bir user oluşturur ve sql server login user ilişkilerini bu SID üzerinden gerçekleştirir.

1. SQL Server seviyesinde bir Login oluştur (SQL Authentication ile)

CREATE LOGIN test_login WITH PASSWORD = 'StrongPassword123';

2. Belirli bir veritabanına gidip, Login’e karşılık gelen bir User oluştur.

USE TestDB;
CREATE USER testuser FOR LOGIN testlogin;

Aşağıdaki komut ile veritabanı altında bulunan userları ve bu user’a bağlı loginleri detaylı bir şekilde görebiliriz.

use Database_Name
SELECT dp.name AS DatabaseUser, 
       sp.name AS LoginName, 
       sp.type_desc AS UserType, 
       DB_NAME() AS DatabaseName
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp 
       ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G') -- SQL User, Windows User, Windows Group
ORDER BY dp.name;

Şimdi önceden oluşturmuş olduğumuz KULLANICI loginini herhangi bir veritabanı üzerinde yetki verelim.

İlgili veritabanı altında yetkimizi verdikten sonra yetki vermiş olduğumuz veritabanı altında bu kullanıcının geldiğini görmüş oluyoruz.

Burada şöyle bir senaryo yapalım  bizim şuan ki KULLANICI login’in sid’si aşağıdaki gibidir.

select sid,name from sys.syslogins where name= 'KULLANICI'

İlgili veritabanımızı detach attach yöntemiyle koparılım sql server’dan  başka bir sql server’a ekleyelim.

S1 Sunucusundaki KULLANICI login’ninin isim olarak aynısını  S2 sunucusunda oluşturuyorum ve veritabanını S2 sunucusundaki sql server’a attach ediyorum.

Attach edeceğim kullanıcı sid yukarıda gözükmektedir S2 sunucusunda aynı isimde oluşturduğum yeni kullanıcının sid değerinin farklı olduğunu aşağıdaki resimde görülmektedir.

Kullanıcıya user mapping ile  AdvantureWork2014 eklediğimde aşağıdaki hatayı vermiş oldu.

Hata ilgili veritabanı altında aynı isimde  kullanıcın bulunduğunu söylüyor. Bu hatadan sonra ilgili veritabanı altında  kontrol işlemini yaptıktan sonra KULLANICI login’nini görüyorum.

Not: Veritabanı altında security kısmında dbo kullanıcısı veritabanı oluşturan kullanıcı olmaktadır. Bu sebepten daha sonra dbo kullanıcı için herhangi bir işlem yapılamamaktadır. Aşağıdaki resimde owner olan dbo user’ının yetkili olduğu kişidir. Owner’ı değiştirilirse veritabanının dbo sahibide değişmektedir.

Bu sorunu gidermek için ilgili veritabanı altında aynı isimdeki user’ı silebiliriz. Ama silerken şunu da göze almak gerekiyor ben ilk sunucumda yani S1 sunucusunda veritabanı altındaki user’a bazı özel yetkiler vermiştim bu bir yetkide olabilir 10’larcada olabilir.

Veritabanını Attach ettiğim İkinci sunucumdaki veritabanı altında detach ettiğim ilk sunucudaki veritabanına ne yetki vermişsem veritabanı>security>user altında  gözükür ama veritabanı altındaki user ile instance bazlı login(Yeni sunucuda login sıfırdan oluşturuldu) ayni sid’e sahip olmadığı için ilişki kuramaz. Bu yüzden veritabanı altındaki user’ı silmek  yararlı bir çözüm değildir. Bunun için veritabanı altındaki user’ın yeni S2 sunucusundaki oluşturulmuş olan instance  login’nine bağlı olduğunu belirtmek gerekiyor.

USE AdventureWorks2014
ALTER USER KULLANICI WITH LOGIN = KULLANICI

User ve login isimleride farklı olabilir bu komut ile ikisinin sid lerini aynı yapıyoruz. Gerçek sistemlerde kullanıcı herhangi bir restore sırasında düşerse bu komutlar çalışır.

Bu komut ile user’ı silmemize gerek kalmıyor çalıştırıp işlemlerimizi otomatikmen yapıyoruz.

USE AdventureWorks2014

EXEC sp_change_users_login 'Auto_Fix', 'KULLANICI'

Bu komut İlk başta aynı isimde bir login var mı diye bakıyor varsa eşleştiriyor yoksa sid’sine bakıyor oda aynı değilse aynı isimde yeni bir login oluşturuyor.

Sid işleriyle uğraşmamak için sp_help_revlogin() komutluyla KULLANICI loginin create scprittini hashlenmiş bir şekilde ikinci sunucuya alırsak ve bu işimle ve aynı sid ile oluşturursak sorun çözülmüş olur. Always on sistemlerde bu şekilde yapılmasında fayda var.

Sp_help_revlogin ile gelen hexcadecimal sayı için bu store procedure yüklenmesi gerekiyor.

Microsoft’un sayfasından ilgili stored procedure’leri indirerek  bu işlemlerimiz gerçekleştiririz. İlgili script’i kopyalayıp sunucumuzda çalıştırmamız lazım. LINK

EXEC sp_help_revlogin 'LOGIN_ISMI'

select name, SID from sys.sql_logins

Yukarıdaki komutla loginlerin sid’leri gözüküyor buradan hangi login’i başka sunucuda oluşturmak istiyorsak ilgili kullanıcının sid’sini alıp aşağıdaki create komutuyla başka bir sunucuda oluşturulabilir.

Ya da manuel bir şekilde oluşturulabilir sid değerini aldıktan sonra.

create login login_ismi with password='Password',SID=0x499243FB6A85EF4B93909111225648D9

Kullanıcı primary ve secondary sunucularında aynı login oluşturmak için sp_help_revlogin’login_adı’  komutunu kullanabilir. İlk sunucuda login şifresi ne ise ikinci sunucudada sql server login olabiliyoruz.

Sp_help_revlogin ‘kullanıcıadı’ bu bize loginin create scpritini haslenmiş bir şekilde veriyor. İkinci sunucu üzerinde aynı veritabanı varsa veritabanı bazında tüm yetkiler gelmiştir. Ama login Server Roles kısmında yetkiler gelmez.

SID yüklendiği kullanıcı üzerinde bulunan server roller resimlerde görüldüğü gibi gelmez. Database rollerinde bir sakınca yoktur.

Sp_help_revlogin ile almış olduğumuz  create script’ti ikinci makinede çalıştırdığımızda aynı kullanıcı oluşuyor şifresiyle birlikte login olabilirim. Bunu yapmayıp sadece o loginin create scprittini alsaydık ikinci makinede kullanıcı oluşurdu ama aynı şifreyle  login olamazdık.

Server seviyesinde loginler ve veritabanı seviyesinde userlar.

select*from sys.server_principals
select*from sys.database_principals

Veritabanı bazında  deneme user’ın sid’sini veriyor.

use AdventureWorks2012
select name,sid from sys.sysusers where name ='login_adı'

Burada  server bazında login bilgisi veriyor.

use master 
select name,sid from sys.syslogins where name ='login_adı'

Şimdi gerçek bir sistemde taşınma işlemi nasıl olur.

• Sp_help_revlogin ‘KARSILAMA’ kullanıcını create script’i alınır. İkinci sunucuda çalıştırılır.

• Loginin tüm instance bazında yetkileri alınır ikinci sunucuda çalışıtırılır. Biz burada KARSILAMA login’ninin  role bazında yetkisini aldık. İkinci sunucuda çalıştırdım. Tüm sunucudaki tüm login role’lerinin hepsi kopyalayıp yapıştırılır.

Neden bu komutu kullanıyoruz. Çünkü sp_help_revlogin procedure ile server rolle gelmez aşağıdaki komutlarla bu rolle alınıp çalıştırılır.

SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''';
' AS [-- Roles To Be Assigned --]
FROM master.sys.server_role_members SRM
INNER JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
    JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
        AND SL.name NOT LIKE '##%##'
        AND SL.name NOT LIKE 'NT AUTHORITY%'
        AND SL.name NOT LIKE 'NT SERVICE%'
        AND SL.name <> ('sa')
        AND SL.name <> 'distributor_admin';

Sadece belirli bir kullanıcının server bazında yetkilerini görmek istersek aşağıdaki komut kullanılmaktadır.

DECLARE @LoginName NVARCHAR(100) = 'Y'; -- Buraya kullanıcı adını yaz
SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''';'
AS [-- Roles To Be Assigned --]
FROM master.sys.server_role_members SRM
INNER JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.name = @LoginName; -- Sadece belirlenen kullanıcı için filtrele

• Aşağıdaki komut veritabanı altında bulunan user ve login create scriptlerini vermektedir.

USE AdventureWorks2014
SELECT 'USE '+ DB_NAME()+'; CREATE USER ['+dp.name+'] FOR LOGIN ['+dp.name+'];'+ 
        'ALTER USER ['+dp.name+'] WITH DEFAULT_SCHEMA=['+dp.default_schema_name+'];' AS [-- Logins To Be Created --]
FROM sys.database_principals AS dp
INNER JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE (dp.type in ('S','G','U'))
        AND dp.name NOT LIKE '##%##'
        AND dp.name NOT LIKE 'NT AUTHORITY%'
        AND dp.name NOT LIKE 'NT SERVICE%'
        AND dp.name <> ('sa')
        AND dp.default_schema_name IS NOT NULL
        AND dp.name <> 'distributor_admin'
        AND dp.principal_id > 4

USE AdventureWorks2014; CREATE USER [KARSILAMA] FOR LOGIN [KARSILAMA];ALTER USER [KARSILAMA] WITH DEFAULT_SCHEMA=[dbo];

• Burada ise belirlemiş olduğumuz veritabanı altındaki  kullanıcıların izinlerini görmekteyiz.

SELECT 'USE '+ DB_NAME()+'; '+CASE WHEN dp.state <> 'W' THEN dp.state_desc ELSE 'GRANT' END +' ' + 
        dp.permission_name + ' TO ' + QUOTENAME(dpg.name) COLLATE database_default + 
        CASE WHEN dp.state <> 'W' THEN '' ELSE ' WITH GRANT OPTION' END +';' AS [-- Permission To Be Assign to the User --]
FROM    sys.database_permissions AS dp
INNER JOIN sys.database_principals AS dpg ON dp.grantee_principal_id = dpg.principal_id
WHERE   dp.major_id = 0 AND dpg.principal_id > 4
        AND (dpg.type in ('S','G','U'))
        AND dpg.name NOT LIKE '##%##'
        AND dpg.name NOT LIKE 'NT AUTHORITY%'
        AND dpg.name NOT LIKE 'NT SERVICE%'
        AND dpg.name <> ('sa')
        AND dpg.default_schema_name IS NOT NULL
        AND dpg.name <> 'distributor_admin'
        AND dpg.principal_id > 4
ORDER BY dpg.name

Not: İnstance seviyesinde bulunan login’i disable moduna çektiğimizde çarpı işareti olmasına rağmen yetkili olduğu veritabanı altında görünmez. Sebebi ise sql server sadece instance seviyesinde veritabanını kontrol etmesi, veritabanı seviyesinde user’ların aktif pasif olma durumuna bakmaz. Eğer veritabanı altında bulunan user’ında çarpı olmasını istiyorsak revoke connect to login_adı yazılması gerekmektedir. Ama bu şekilde sql server’a disable edilen loginle connect olduktan sonra veritabanı altında user çarpı işareti olsa bile veritabanına bağlantı gerçekleştirir.(Tabi burada loginin veritabanı üzerinde db_owner veya sys_admin yetkisi gerekli) Tam erişilmez olması isteniyorsa DENY CONNECT TO login_adı şeklinde olması gerekmektedir. Burada kullanıcının veritabanı üzerinde en maksimum yetkileri olsa bile bağlanamaz. Tekrar veritabanı altındaki kullanıcıyı aktif etmek için GRANT CONNECT TO login_adı şeklinde olması gerekmektedir.

Not: Aşağıdaki komut ile anlık çalışan sorgularda belirtilen login’le ilgili değerleri getirmektedir.

EXEC sp_WhoIsActive
    @filter_type = 'login',
    @filter = 'klnOris'

Herhangi bir loginin default schema yapısını değiştirmek için aşağıdaki resimdeki schema kısmında ilgili schema seçilmektedir. Farklı bir schema yapısını oluşturmak için veritabanı altında schemas kısmında ayarlanmaktadır.

Başka bir makalede görüşmek dileğiyle..

“Yavrucuğum, namazını özenle kıl, iyi olanı emret, kötü olana karşı koy, başına gelene sabret. İşte bunlar, kararlılık gerektiren işlerdendir.”Lokman-17