Use DBC.RoleMembers
Select RMM.Grantee As UserName
, RMM.RoleName
, ARR.AccessRight
, ARR.Databasename
, ARR.Tablename
From DBC.RoleMembers RMM
Join DBC.AllRoleRights ARR
ON RMM.RoleName = ARR.RoleName
Where ARR.Databasename = '?DBName'
And ARR.Tablename = '?TblName'
Union
Select ALR.Username
, Null
, ALR.Accessright
, ALR.Databasename
, ALT.Tablename
Where ARR.Databasename = '?DBName'
And ARR.Tablename = '?TblName'
Order By 4,5,1,3,2
;
Thanks Jimm..
But query following UNION is missing table names.. Can you check that..
Apologies - it was late!
Select RMM.Grantee As UserName
, RMM.RoleName
, ARR.AccessRight
, ARR.Databasename
, ARR.Tablename
From DBC.RoleMembers RMM
Join DBC.AllRoleRights ARR
ON RMM.RoleName = ARR.RoleName
Where ARR.Databasename = '?DBName'
And ARR.Tablename = '?TblName'
Union
Select ALR.Username
, Null (Char(30))
, ALR.Accessright
, ALR.Databasename
, ALR.Tablename
From DBC.AllRights ALR
Where ALR.Databasename = '?DBName'
And ALR.Tablename = '?TblName'
Order By 4,5,1,3,2
;
thanks again Jimm...your script is good when we want to know a user's explicit rights either directly or via role where as my question is - how to know which user/role is having explicit rights on a particular table? Any insight here?
Using the script above we could do something like this :
Select RMM.Grantee
, RMM.RoleName
, ARR.AccessRight
, ARR.Databasename
, ARR.Tablename
From DBC.RoleMembers RMM
Join DBC.AllRoleRights ARR
ON RMM.RoleName = ARR.RoleName
Where ARR.Databasename = '<dbname here>'
And (ARR.Tablename = 'All' or ARR.Tablename = '<tablename here>')
AND RMM.Grantee = '<userID here'
----------------- This catches the "ALL" case as well as the actual table name but may have a drawback as it allows for duplicates --------------------------------------
and to remove duplicates try this
Select distinct(RMM.RoleName), RMM.Grantee , ARR.AccessRight From DBC.RoleMembers RMMJoin DBC.AllRoleRights ARR
ON RMM.RoleName = ARR.RoleName
Where ARR.Databasename = '<DBname here>'
And (ARR.Tablename = 'All' or ARR.Tablename = '<TABLE NAME HERE>')
AND RMM.Grantee = '<USER ID HERE>'
- I know this is way late but it helps to share this info for the next person who needs it. Work smarter! Google it!
Hi All
What I want oto know
Iam intrested to know all the explicit access rights on a given table covering individual and role privileges.
Say for eg - UserA and RoleA have insert rights on Table1.
Then what should I query to get the resultset showing UserA and RoleA with their access.
What I know
1.Using DBC.ALLRIGHTS and DBC.ALLROLERIGHTS , by providing a username , we can get all the explicit rights granted to user either directly or via role.
2.Specifying tablename in WHERE clause of DBC.ALLRIGHTS , the resultset gives us the user name and its rights having automatic access.
Above question is similar but the focus is explicit rights on an an object covering individual users and roles.