Background

Trying to run SQL Server Data Tools with a low privileged user and ran into this problem.

 

Error Message

Image

 

Text

Error Messages Warning The login for the target does not have the VIEW ANY DEFINITION permission. The comparison will be restricted to database scoped elements if the source is a database.  The SELECT permission was denied on the object ‘certificates’, database ‘mssqlsystemresource’, schema ‘sys’.  The SELECT permission was denied on the object ‘symmetric_keys’, database ‘mssqlsystemresource’, schema ‘sys’.

 

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:

1. 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:

1. Unfortunately, to me, using the same command, revoke, to deny and deactivate the denial is not nearly as intuitive as it could be
2. But, it works

 

Again, Review Permission select on sys.certificates

Code

exec sp_helprotect 'sys.certificates'

Output:

Explanation:

1. 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.