Bu makalede mssql server üzerinde sp_help_revlogin procedure ile gelmeyen server seviyesindeki yetkileri ve logine bağlı mevcut user’ın veritabanı üzerindeki yetkilerinide script ile görmüş olacağız. Aşağıdaki komut ile 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';
Tüm loginler için aşağıdaki komut ile rahatlıkla göz yormadan server seviyesinde yetkileri görebiliriz.
SELECT
sp.name AS LoginName,
sp.type_desc AS LoginType,
MAX(CASE WHEN sr.name = 'sysadmin' THEN 'YES' ELSE 'NO' END) AS sysadmin,
MAX(CASE WHEN sr.name = 'securityadmin' THEN 'YES' ELSE 'NO' END) AS securityadmin,
MAX(CASE WHEN sr.name = 'serveradmin' THEN 'YES' ELSE 'NO' END) AS serveradmin,
MAX(CASE WHEN sr.name = 'setupadmin' THEN 'YES' ELSE 'NO' END) AS setupadmin,
MAX(CASE WHEN sr.name = 'processadmin' THEN 'YES' ELSE 'NO' END) AS processadmin,
MAX(CASE WHEN sr.name = 'diskadmin' THEN 'YES' ELSE 'NO' END) AS diskadmin,
MAX(CASE WHEN sr.name = 'dbcreator' THEN 'YES' ELSE 'NO' END) AS dbcreator,
MAX(CASE WHEN sr.name = 'bulkadmin' THEN 'YES' ELSE 'NO' END) AS bulkadmin
FROM sys.server_principals sp
LEFT JOIN sys.server_role_members srm
ON sp.principal_id = srm.member_principal_id
LEFT JOIN sys.server_principals sr
ON srm.role_principal_id = sr.principal_id
WHERE sp.type IN ('S','U','G') -- SQL Login, Windows User, Group
AND sp.name NOT LIKE '##%##'
AND sp.name NOT LIKE 'NT AUTHORITY%'
AND sp.name NOT LIKE 'NT SERVICE%'
GROUP BY sp.name, sp.type_desc
ORDER BY sp.name;
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ı yazı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.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
Bu makalede kullanıcının instance ve veritabanı seviyesinde yetkilerini görmüş olduk. Başka makalede görüşmek dileğiyle…
İslam’ın temelini güzel ahlâk oluşturur; insanın davranışları inancının aynasıdır.(Hadis)
