Aşağıdaki fonksiyon ile SQL Server 2008 üzerindeki bir kullanıcının hangi yetkilerinin olduğunu rahatlıkça öğrenebilirsiniz.
CREATE FUNCTION dbo.fnSecurity_UserRoles(@UserName nvarchar(80))
RETURNS @Result TABLE (Role nvarchar(80),isDBRole bit)
AS BEGIN
IF @UserName IS NULL
SET @UserName = system_user
DECLARE @member_principle_id int
SELECT @member_principle_id = principal_id
FROM sys.database_principals
WHERE name = @UserName
INSERT INTO @Result(Role,isDBRole)
SELECT p.name,1 FROM sys.database_role_members r
JOIN sys.database_principals p on p.principal_id = r.role_principal_id
WHERE member_principal_id= @member_principle_id
SELECT @member_principle_id = principal_id
FROM sys.server_principals
WHERE name = @UserName
INSERT INTO @Result(Role,isDBRole)
SELECT p.name,0
FROM sys.server_role_members m
join sys.server_principals p on m.role_principal_id = p.principal_id
WHERE m.member_principal_id = @member_principle_id
RETURN
END
GO
Kullanıcıyı listelemek için ;
SELECT * FROM dbo.fnSecurity_UserRoles('fatih')
Ya da aktif kullanıcının o an ki rollerini getirebilirsiniz,
SELECT system_user, * FROM dbo.fnSecurity_UserRoles(system_user)
Hepinize kolay gelsin.
Hiç yorum yok:
Yorum Gönder