February 10, 2011 at 4:08 pm
I can't seem to query system tables to tell me whether a particular login/user has VIEW DEFINITION permissions Granted or Denied. How do I do that?
For example, I realize that if a user has SELECT permissions that they should be able to 'see' the object in Object Explorer. But if the user can't I'd like to know how to see if they happen to have DENY VIEW DEFINITION permissions. Is that possible?
February 14, 2011 at 8:13 am
Does the below script help?
select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
sys.database_permissions.permission_name,
sys.database_permissions.state permission_state,
sys.database_permissions.state_desc,
state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id =
sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id =
sys.database_principals.principal_id
order by 1, 2, 3, 5
__________________________
Allzu viel ist ungesund...
February 14, 2011 at 10:33 pm
Nope. You'll get database roles from that but not users or groups right? Even if it did, it wouldn't show whether a user/group had select rights but also had a DENY VIEW DEFINITION...
February 15, 2011 at 12:19 am
dboProduct Sales for 1997testerVW VIEW DEFINITIONDDENYDENY VIEW DEFINITION on [dbo].[Product Sales for 1997] to [tester]
The result set above is what you are asking for if I understand you well. If not, then ignore my post and let's see what others say.
__________________________
Allzu viel ist ungesund...
April 12, 2011 at 7:20 pm
Use this code:
SELECT l.name as grantee_name, p.state_desc, p.permission_name
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
WHERE permission_name = 'VIEW ANY DEFINITION' ;
GO
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply