Background
Trying to run SQL Server Data Tools with a low privileged user and ran into this problem.
Error Message
Image
Text
|
Remediation
Error – View any definition
To fix the “View any definition warning” we will issue:
use master go Grant VIEW ANY DEFINITION to [user]; go
Error – Select
The “The SELECT permission was denied on the object ‘certificates’, database ‘mssqlsystemresource’, schema ‘sys’.” was far more perplexing.
Capture SQL Generated by SSDT
Here is the SQL Sent by SSDT.
select certificate_id id, null as id2, 'CERT' as type, name as name1, null as name2, null as name3, principal_id as a1, binary_checksum(pvt_key_encryption_type, thumbprint) as v1, null as mod from sys.certificates
Investigate Permission Set on sys.certificates
Code
exec sp_helprotect 'sys.certificates'
Output:
Grant select on sys.certificates
Code
grant select on sys.certificates to [ssdtuser];
Review Permission select on sys.certificates
Code
exec sp_helprotect 'sys.certificates'
Output:
Explanation:
- Can’t show my user name, but believe me it is the entry #4. And, it has a grant next to it
Tried running ssdt
Tried running ssdt again, but same problem.
Root of Problem
The root of the problem is that deny unlike revoke privilege is an enduring refuse.
Though, we granted select permission, any encompassing deny will mute our grant.
To fix
revoke select on sys.certificates from public go
Explanation:
- Unfortunately, to me, using the same command, revoke, to deny and deactivate the denial is not nearly as intuitive as it could be
- But, it works
Again, Review Permission select on sys.certificates
Code
exec sp_helprotect 'sys.certificates'
Output:
Explanation:
- We rid ourselves of the deny on public group
Quick Summary
There is a bit of difference between Deny and Revoke.
The sysadmin user, sa, hides some of the differences.
But, once you try to use less privileged users, you might find that you have to relax enduring denials.