Καθημερινά σαν DBA μου έρχονται πολλά αιτήματα που αφορούν την ασφάλεια των SQL Server instances και των database που αυτά έχουν.
Ένα σύνηθες ερώτημα είναι να βγάλω ένα report για τους χρήστες με τις προσβάσεις που έχουν σε συγκεκριμένη database.
Κάτι τέτοιο το έχω μοιραστεί μαζί σας εδώ
Επειδή όμως δουλεύω συνήθως (για να μην πω αποκλειστικά) με windows groups στο παραπάνω report βλέπω μόνο τα groups. Αρκετές φορές όμως θέλω να ξέρω και τα windows accounts που ανήκουν σε αυτό το windows group.
Μέχρι τώρα αν ήθελα κάτι τέτοιο απλά εκτελούσα την xp_logininfo για κάθε group του οποίου ήθελα να δω τα accounts. Σήμερα έπεσα σε μια βάση που είχε πολλά όμως windows groups και ήθελα να τα δω όλα. Σαν τεμπέλης που είμαι είπα να φτιάξω κάτι quick & dirty για να πάρω συγκεντρωτικά την πληροφορία. Έτσι έφτιαξα το παρακάτω
declare @cmd nvarchar(max)='';
create table #t
(
[account name] nvarchar(100)
, [type] nvarchar(20)
, [privilege] nvarchar(20)
, [mapped login name] nvarchar(100)
, [permission path] nvarchar(100)
);
select @cmd += 'insert into #t exec xp_logininfo ''' + name + ''',''members'';' from sys.database_principals where type='G';
exec (@cmd);
select * from #t;
drop table #t;
Παρόλο που αυτό έκανε μια χαρά την δουλειά του αποφάσισα να το ενσωματώσω στην υπάρχουσα stored procedure.
Αρχικά έβαλα τον παραπάνω κώδικα στο τέλος της stored procedure και το αποτέλεσμα ήταν να πάρω δύο result sets.
Ικανοποιητικό αποτέλεσμα αλλά τελικά αποφάσισα να έχω ένα αποτέλεσμα το οποίο να έχει και τα δύο μαζί το οποίο είναι το παρακάτω
USE master;
GO
ALTER PROC dbo.sp_GetAllObjectsUsersPerms
AS
declare @cmd nvarchar(max)='';
create table #t
(
[account name] nvarchar(100)
, [type] nvarchar(20)
, [privilege] nvarchar(20)
, [mapped login name] nvarchar(100)
, [permission path] nvarchar(100)
);
select @cmd += 'insert into #t exec xp_logininfo ''' + name + ''',''members'';' from sys.database_principals where type='G';
exec (@cmd);
with r as (
SELECT
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[LoginName] = ulogin.[name],
[Role] = NULL,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc]
ELSE perm.[class_desc]
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name]
WHEN 4 THEN imp.[name]
ELSE OBJECT_NAME(perm.[major_id])
END,
[ColumnName] = col.[name]
FROM
sys.database_principals AS princ
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid]
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G')
AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
UNION
SELECT
[UserType] = CASE membprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = membprinc.[name],
[LoginName] = ulogin.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc]
ELSE perm.[class_desc]
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name]
WHEN 4 THEN imp.[name]
ELSE OBJECT_NAME(perm.[major_id])
END,
[ColumnName] = col.[name]
FROM
sys.database_role_members AS members
JOIN sys.database_principals AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN sys.database_principals AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = membprinc.[sid]
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
membprinc.[type] IN ('S','U','G')
AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
UNION
SELECT
[UserType] = '{All Users}',
[DatabaseUserName] = '{All Users}',
[LoginName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.[type_desc]
ELSE perm.[class_desc]
END,
[Schema] = objschem.[name],
[ObjectName] = CASE perm.[class]
WHEN 3 THEN permschem.[name]
WHEN 4 THEN imp.[name]
ELSE OBJECT_NAME(perm.[major_id])
END,
[ColumnName] = col.[name]
FROM
sys.database_principals AS roleprinc
LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id]
JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id]
LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id]
LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id]
AND col.[column_id] = perm.[minor_id]
LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id]
WHERE
roleprinc.[type] = 'R'
AND roleprinc.[name] = 'public'
AND obj.[is_ms_shipped] = 0
)
select
[UserType],
[DatabaseUserName],
[LoginName],
grp.[account name] as [Windows Account],
[Role],
[PermissionType],
[PermissionState],
[ObjectType],
[Schema],
[ObjectName],
[ColumnName]
from r
LEFT JOIN #t as grp on grp.[permission path] = [LoginName]
ORDER BY LoginName,[Windows Account];
drop table #t;
GO
-- make it system object
EXEC sys.sp_MS_marksystemobject sp_GetAllObjectsUsersPerms ;
GO