MSSQL Server Login ve User Oluşturma

Bu makalede mssql server üzerinde login ve user oluşturma komutlarını ve ne işe yaradıklarına değinmiş olacağız. Login 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;

3. Windows bazında bir kullanıcı eklemek için aşağıdaki komutlar kullanılmaktadır.

CREATE LOGIN [ServerName\Wuser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

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.

Login’in user mapping bölümünde hangi db altında tanımlanmasını istiyorsak onu seçeriz ve aynı isimde user oluşur. Ayrıca user ismini kendimizde ssms arayüzünden ya da  aşağıda bulunan script yardımıyla yapabiliriz.

İlgili veritabanı altında Login yetkimizi verdikten sonra yetki vermiş olduğumuz veritabanı altında bu Login’e bağlı User geldiğini görmüş oluyoruz.

Dikkat edersek veritabanı altında bizim login ismimizle aynı gelir yukarıdaki şekilde oluşturduktan sonra tekrardan değiştirme yapacağız. İsmini YU olarak değiştiriyoruz. Bu değişim veritabanı seviyesinde yapılmaktadır.

Bu kod önceden veritabanı altında oluşan user name ismini değiştirmektedir.

USE [AdventureWorks2014]
GO
ALTER USER [KULLANICI] WITH NAME=[YU]
GO

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.

Sql server üzerinde bulunan loginlerin create ve user işlemleriyle uğraşmayıp Sp_help_revlogin procedure ile loginlerimiz veritabanı altında bulunan user’la birlikte oluşturulmaktadır..

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.

Manuel bir şekilde loginin sid değeri ile birlikte aşağıdaki komut ile oluşturulabilir.

create login LoginName 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 sunucuda da 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.

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'

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