0

I have an SQL Server 2016 database. This database is hosted on a shared server, and is managed by a central DBA team. In an effort to harden data security on the Production server, the DBA team has taken the approach to revoke/disable all public roles (i.e. DENY everything to the public role).

I believe this was done to limit (meta)data access to other databases on the same server, presumably along these lines: Is it best practice to revoke server permissions to the server "public" role on SQL Server instances?

The problem is that this setup prevents IntelliSense from working on Microsoft SQL Server Management Studio. I think it's the situation Aaron Bertrand cautions on here: https://dba.stackexchange.com/a/116814/180921

I have elevated access rights (RWED) to my database, but am not a full admin (this role is restricted to members of the DBA Production team).

Is there a possible solution here? How can IntelliSense be made to work on my database, while respecting DBA desire to limit metadata view for other databases

9
  • Exactly what are your elevated access rights to your database?
    – Dan Guzman
    Jul 18, 2022 at 15:07
  • Sounds like they've denied read access to system tables for this database, also you shouldn't need to DENY just REVOKE Jul 18, 2022 at 15:21
  • @DanGuzman I am a member of the Admin AD group. This group has: db_datareader, db_datawriter, db_ddladmin, public, rd_execute
    – Roberto
    Jul 18, 2022 at 15:46
  • 1
    db_owner won't ignore DENY. Only the real dbo ignores permission checks. Jul 18, 2022 at 16:57
  • 1
    @Roberto, your choices are either to change the database owner to your login (ALTER AUTHORIZATION ON DATABASE::YourDatabase to Roberto;) or remove DENY to public on your database's system objects. The database-scoped public role does applies only to your database, not the other databases on the instance.
    – Dan Guzman
    Jul 20, 2022 at 19:01

1 Answer 1

0

The DBA Production team was finally able to solve this! They did not make changes to the public role, but rather they implemented some specific permissions to specific users/groups:

grant execute on xp_regread to [USER_ACCOUNT_TO_ENABLE_ON]
grant execute on xp_qv to [USER_ACCOUNT_TO_ENABLE_ON]
grant execute on xp_instance_regread to [USER_ACCOUNT_TO_ENABLE_ON]
grant execute on xp_msver to [USER_ACCOUNT_TO_ENABLE_ON]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.