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

Roberto
  • 119
  • 3

1 Answers1

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]
Roberto
  • 119
  • 3