Bu makalede Loginin instance,server bazında hangi yetkilere sahip olduğunu görmüş olacağız.
1.
Aşağıdaki komut bağlı olduğumuz kullanıcının instance üzerinde hangi yetkilere sahip olduğunu görebiliriz.
SELECT entity_name, permission_name
FROM sys.fn_my_permissions(NULL, database_veya_serverbazında yazılır.)
Farklı kullanıcılar’la login olunduğu zaman sayı farkları anlaşılıyor.
Kullanıcımızın server bazında hangi yetkilere sahip olduğunu gösteriyor
execute as login = 'denemeuser'
select*from sys.fn_my_permissions(null,'server')
revert
goNot: Herhangi bir login’e control server yetkisi verdiğimizde bu login sysadmin kullanıcısıymış gibi davranır. Linked server oluşturabilir jobları oluşturabilir mail konfigürasyonu oluşturabilir bunun gibi birden fazla özelliği olabilir.
2.
Aşağıdaki komut ile loginlerin instance üzerindeki yetkileri görebiliriz. Microsoft sayfasından alınmıştır. Login’in permisson_name kısmındaki yetkilerini baz almaktadır.
USE [master]
SELECT pr.principal_id,
pr.name, pr.type_desc,
pe.state_desc,
pe.permission_name
FROM sys.server_principals AS pr
JOIN sys.server_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
3.
Sql server instance üzerinde bulunan tüm loginler bağlantılı olduğu database’ler ve veritabanı altındaki user isimleri microsoft’un sayfasında bulmuş olduğum aşağıdaki kodda mevcuttur. Default database kolunu login ilk oluşturulurken seçilen database kısmıdır.
DECLARE @t TABLE
(
[Server Login] VARCHAR(200)
, [DB User] VARCHAR(200)
, default_database_name VARCHAR(200)
, dbName VARCHAR(200)
);
DECLARE
@rows INT
, @row INT = 1
, @dbName sysname
, @sql VARCHAR(MAX);
DECLARE @dbS TABLE (id INT IDENTITY PRIMARY KEY, dbname sysname);
INSERT INTO @dbS (dbname) SELECT name FROM sys.databases ORDER BY name;
SET @rows = @@ROWCOUNT;
WHILE @row <= @rows
BEGIN
SELECT @dbName = dbname FROM @dbS WHERE id = @row;
-- SET @sql = 'USE ' + QUOTENAME(@dbName);
BEGIN TRY
-- EXECUTE (@sql);
SET @sql = 'SELECT
sp.name AS [Server login]
, dp.name AS [DB user]
, sp.default_database_name
, ' + QUOTENAME(@dbName, '''') + '
FROM ' + QUOTENAME(@dbName) + '
.sys.server_principals sp
JOIN ' + QUOTENAME(@dbName) + '.sys.database_principals dp
ON sp.sid = dp.sid
ORDER BY
sp.name;'
INSERT @t
EXECUTE (@SQL);
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH;
SET @row = @row + 1;
END;
SELECT * FROM @t;
4.
Aşağıdaki komut ile instance altında bulunan sysadmin kullanıcılarını ve oluşturulma tarihlerini görebiliriz.
SELECT SP.name AS LoginName,
type_desc AS LoginType,
Cast(create_date AS DATE) DateCreated
FROM sys.server_principals SP
inner join sys.syslogins SL
on SP.sid=SL.sid
WHERE SL.sysadmin=1
5.
Aşağıdaki komut ile veritabanını hangi kullanıcı oluşturduğu ve oluşturma tarihini görebiliriz.
select d.name VeritabaniAdi,
l.name OlusturanKullanici,
d.create_date OlusturmaTarihi
from sys.databases d
join sys.syslogins l on l.sid=d.owner_sid
order by d.name
6.
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
7.
İnstance üzerinde Veritabanı Seviyesinde Yetkilendirilmiş Kullanıcıları Görmek İçin:
EXEC sp_msforeachdb N'
USE [?]; -- Bu, her veritabanı için dinamik olarak çalışacak.
BEGIN
PRINT ''Veritabanı: [?]''; -- Veritabanı adı burada belirtilecek
BEGIN TRY
SELECT
''[?]'' AS DatabaseName, -- Veritabanı adı burada belirtilecek
sp.name AS ServerLogin, -- Server login
dp.name AS UserName, -- Veritabanı kullanıcı adı
dp.type_desc AS UserType, -- Kullanıcı tipi (SQL USER, Windows USER)
dp.default_schema_name AS DefaultSchema, -- Varsayılan şema
dr.name AS DatabaseRole, -- Kullanıcının rolü
p.permission_name AS PermissionName, -- Kullanıcının izni
p.state_desc AS PermissionState -- İznin durumu (GRANT, DENY, REVOKE)
FROM
sys.database_principals dp
LEFT JOIN
sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
LEFT JOIN
sys.database_principals dr ON drm.role_principal_id = dr.principal_id
LEFT JOIN
sys.database_permissions p ON dp.principal_id = p.grantee_principal_id
LEFT JOIN
sys.server_principals sp ON dp.sid = sp.sid
WHERE
dp.type IN (''S'', ''U'')
AND dp.name NOT IN (''dbo'', ''INFORMATION_SCHEMA'', ''sys'', ''sa'', ''guest'',
''##MS_PolicyEventProcessingLogin##'',
''NT SERVICE\SQLServerReportingServices'')
ORDER BY
sp.name, dp.name, dr.name, p.permission_name;
END TRY
BEGIN CATCH
PRINT ''Hata: [?] veritabanına erişilemedi veya sorgu başarısız oldu.'';
END CATCH
END';
8.
İnstance üzerinde Şema Seviyesinde Yetkilendirilmiş Kullanıcıları Görmek İçin aşağıdaki komut kullanılmaktadır.
EXEC sp_msforeachdb N'
USE [?]; -- Bu, her veritabanı için dinamik olarak çalışacak.
BEGIN
PRINT ''Veritabanı: [?]''; -- Veritabanı adı
BEGIN TRY
SELECT
''[?]'' AS DatabaseName, -- Veritabanı adı burada belirtilecek
dp.name AS UserName,
dp.type_desc AS UserType,
dp.default_schema_name AS DefaultSchema,
p.class_desc AS ObjectType,
OBJECT_NAME(p.major_id) AS ObjectName,
p.permission_name AS PermissionName,
p.state_desc AS PermissionState
FROM
sys.database_principals dp
JOIN
sys.database_permissions p ON dp.principal_id = p.grantee_principal_id
WHERE
dp.type IN (''S'', ''U'')
AND dp.name NOT IN (''dbo'', ''INFORMATION_SCHEMA'', ''sys'', ''sa'', ''guest'',
''##MS_PolicyEventProcessingLogin##'',
''NT SERVICE\SQLServerReportingServices'')
AND p.class_desc = ''SCHEMA'' -- Şema seviyesinde izinleri göster
ORDER BY
dp.name, p.permission_name;
END TRY
BEGIN CATCH
PRINT ''Hata: [?] veritabanına erişilemedi veya sorgu başarısız oldu.'';
END CATCH
END';
9.
İnsantance üzerinde Nesne Seviyesinde Yetkilendirilmiş Kullanıcıları Görmek İçin:
EXEC sp_msforeachdb N'
USE [?]; -- Bu, her veritabanı için dinamik olarak çalışacak.
BEGIN
PRINT ''Veritabanı: [?]''; -- Veritabanı adı
BEGIN TRY
SELECT
''[?]'' AS DatabaseName, -- Veritabanı adı burada belirtilecek
dp.name AS UserName,
dp.type_desc AS UserType,
p.class_desc AS ObjectType,
OBJECT_NAME(p.major_id) AS ObjectName,
p.permission_name AS PermissionName,
p.state_desc AS PermissionState
FROM
sys.database_principals dp
JOIN
sys.database_permissions p ON dp.principal_id = p.grantee_principal_id
WHERE
dp.type IN (''S'', ''U'')
AND dp.name NOT IN (''dbo'', ''INFORMATION_SCHEMA'', ''sys'', ''sa'', ''guest'',
''##MS_PolicyEventProcessingLogin##'',
''NT SERVICE\SQLServerReportingServices'')
AND p.class_desc IN (''OBJECT_OR_COLUMN'') -- Nesne seviyesinde izinleri göster
ORDER BY
dp.name, p.permission_name;
END TRY
BEGIN CATCH
PRINT ''Hata: [?] veritabanına erişilemedi veya sorgu başarısız oldu.'';
END CATCH
END';
Aşağıdaki script ile tek bir 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_id10.
Aşağıdaki komut ile veritabanı altındaki tüm kullanıcıları ve yetkileri görebiliriz. İlgili veritabanının başlangıçta seçilmesi gerekmektedir.
USE DatabaseName
SELECT
[UserName] = CASE princ.[type]
WHEN 'S' THEN princ.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
[UserName] = CASE memberprinc.[type]
WHEN 'S' THEN memberprinc.[name]
WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
END,
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--Role/member associations
sys.database_role_members members
JOIN
--Roles
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
--Login accounts
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[UserName] = '{All Users}',
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id),
[ColumnName] = col.[name]
FROM
--Roles
sys.database_principals roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
JOIN
--All objects
sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
--Only roles
roleprinc.[type] = 'R' AND
--Only public role
roleprinc.[name] = 'public' AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY
princ.[Name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.[permission_name],
perm.[state_desc],
obj.type_desc--perm.[class_desc] 
11.
Aşağıdaki komut ile veritabanı altındaki kullanıcıların hangi tabloya veya view’e yetkisi olduğunu görebiliriz.
SELECT
[UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.server_principals ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN
sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G') AND
-- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')Not: İnstance’a bağlandıktan sonra veritabanı altında veya instance altında herhangi bir bölüme tıkladığımda aşağıdaki hata ile karşılaşmaktayım.
Bunun sebebi kullanmış olduğumuz SSMS sürümü olabilir. Ben böyle bir sorunla karşılaştığım zaman ssms’i yönetici olarak çalıştırıyorum Sorun çözülmüş oluyor.
Başka bir makalede görüşmek dileğiyle..
“ Ey örtüsüne bürünen! Kalk ve uyar! Sadece rabbinin büyüklüğünü dile getir. Elbiseni tertemiz tut. Her türlü pislikten uzak dur.”Müddessir-1-5
