December 15, 2008 at 3:51 am
Hi,
Can you tell me what is the table name where are kept the information about the security logins and the permissions that are granted to them?
December 15, 2008 at 4:25 am
The following script will produce a list of all the defined permissions in the SQL Server database, linking the role with the role members and displaying their inherited permissions. Will only work for 2005 and 2008.
WITH perms_cte as
(
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name, p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM perms_cte p
WHERE principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM perms_cte p
right outer JOIN (
select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
from sys.database_role_members rm
INNER JOIN sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
order by 1
James Howard
December 15, 2008 at 5:05 am
Thank you for your reply.
That script will be for sure useful. However I need a bit different information. The reason why I need info about store of security login permissions is that I would like to investigate the transaction log in order to check who modified them. I would like to know in what table that would be visible (the change).
To be more clear: if somebody modified the permission of the security group, he/she should modify the table (I guess). I would need to know the name of the table that store this kind information. Next step would be the analize modifications on that table.
December 15, 2008 at 5:36 am
Oh I see...
The sp_helprotect procedure (http://msdn.microsoft.com/en-us/library/ms190310.aspx) returns a field named grantor which identifies the name of the principal that granted permissions.
Hope that helps...
James Howard
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply