Post reply

How can i hide all DBs for a single user but still have them in the SQLAgent MSDB roles?

  • March 11, 2015 at 9:20 am

    #398164

    I've tried "deny view any database to my_user" and that pretty much does the job of hiding DBs, except you still get to view some objects in master and tempdb. (selecting db_denydatareader/writer roles in these dbs, results in a complete failure to even log in).

    the problem is that denying viewing any database also therefore includes MSDB, so that user's SQLAGent msdb role membership is ignored and the Agent disappears too.

    any ideas how i get round this so that a given login can login, see NO database at all, but still have the SQLAgent in their object explorer as per their MSDB role membership?

    thanks

  • March 11, 2015 at 9:08 pm

    #1783509

    You can't do this. SSMS will show databases that the user has access to. You need to make your user a member of (at least) one of the SQL Agent roles in the MSDB database so that they see SQL Server Agent. Hence, they can see the MSDB database in SSMS because they are a member of a role in MSDB.

  • March 12, 2015 at 3:09 am

    #1783523

    Thanks for your reply. That's reluctantly acceptable - what about hiding the other system DBs and user DBs? If msdb has to be visible then that's fine.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply