June 1, 2010 at 8:35 pm
I have several Windows security groups added as server logins to my SQL instance;
these Windows groups are mapped to database roles on my database.
These groups contain multiple individual Windows NT user accounts as members.
I need to find out which database objects each individual Windows user login has access to - by virtue of membership to the security groups - and what kind of permissions those are.
I have the following script, but this only gives me permissions on database objects for the database roles; I want to find permissions on database objects for individual Windows users:
SELECT
object_name(P.major_id) AS [Object]
,R1.[name] AS Grantee
,P.permission_name AS [Action]
FROM
sys.database_permissions P
INNER JOIN
sys.database_principals R1
ON
P.grantee_principal_id = R1.principal_id
INNER JOIN
sys.database_principals R2
ON
P.grantor_principal_id = R2.principal_id
WHERE
object_name(P.major_id) IS NOT NULL
AND
R1.[name] <> 'public';
Here is an example of the info I'm looking for:
Object NT Login Action
-----------------------------------------------
sproc1 domain1\user1 EXECUTE
Anyone have a script like this?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 4, 2010 at 3:05 am
See if this helps:
http://www.sqlservercentral.com/Forums/Topic411310-338-1.aspx
For instance, using Grant's example:
WITH roles (PrincipalID, RoleName, UserName)
AS
(
SELECT p.Principal_id, p.NAME
,m.NAME
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
)
SELECT rl.PrincipalID, rl.RoleName, rl.UserName
, P.permission_name
FROM roles rl
JOIN sys.database_permissions P ON rl.PrincipalID = P.grantee_principal_id
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply