SQL Server Data Tools – Error Message – Select permission denied on the object ‘certificates’, database ‘mssqlsystemresource’, schema ‘sys’.

Background

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

 

Error Message

Image

ErrorList

 

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:

sp_helpprrotect-certificates

 

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:

sp_helpprrotect-certificates-postgrant

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:

sp_helpprrotect-certificates-postrevokedeny

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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s