locked
Unable to view SQL Server Agent while logged in as user RRS feed

  • Question

  • Hello Everyone,

    I have granted SQLAgentUserRole permission to one of the user and using enterprise edition of SSMS. still user is not able to view SQL Server Agent. Any help will be greatly appriciated.

    Thursday, October 16, 2014 4:08 AM

Answers

  • There is no Enterprise Edition of SSMS. There is SSMS Basic and SSMS Complete. Prior to 2012 sp1, only SSMS Basic were available with Express Edition - but as of 2012 sp1 Expredd also offers SSMS Complete. SSMS Complete is selected bu default when you install SSMS (unless you are prior to 2012 sp1 and are using Express, of course).

    However, even SSMS Basic *should* show Agent assuming you have permissions for that. This is hearsay, but from trusted sources. Here is what to do:

    Check what is installed for the machine from where you are running SSMS. You can do that using SQL Server Installation Center - see this blog post: http://sqlblog.com/blogs/tibor_karaszi/archive/2011/02/10/what-does-this-express-edition-look-like-anyhow.aspx  (towards the end).

    On that machine try both this problematic account as well as an account which is sysadmin. Does the sysadmin account see Agent? If so, you know permissions aren't granted properly. If not, then you know the tool is the problme.

    Also try the problematic account from a machine where you know you see Agent normally. Again, this will help you assess whether the problem is the tool (SSMS) or permissions for the account.


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, October 16, 2014 11:38 AM

All replies

  • Hi,

    What edition of SQL Server is this?

    1. If it is 2012 then make sure that person connecting to SQL Server 2012 is using SSMS 2012, there could be compatibility issues when some features are not showing up when connecting with wrong version.

    2. Make sure you have given VIEW ANY DATABASES permission to the user.

    Also go through this article.

    Implement SQL Server Agent Security


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page



    Thursday, October 16, 2014 4:29 AM
  • Hello Everyone,

    I have granted SQLAgentUserRole permission to one of the user and using enterprise edition of SSMS. still user is not able to view SQL Server Agent. Any help will be greatly appriciated.

    Are you using Management Studio (SSMS) Express client to connect? If yes it wont show SQLServer Agent. You should installing SSMS from enterprise version media itself.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Olaf HelperMVP Thursday, October 16, 2014 6:27 AM
    Thursday, October 16, 2014 4:42 AM
  • Hi Kumarjohn,

    for access sql server agent we required below mentioned roles on MSDB database.

    • SQLAgentUserRole

    • SQLAgentReaderRole

    • SQLAgentOperatorRole

    Please assign these roles and let us know the result


    Thanks

    Thursday, October 16, 2014 5:51 AM
  • still user is not able to view SQL Server Agent


    SSMS don't check the permissions on Startup, so as Visakh I guess the Express Edition of SSMS is installed, where the Agent isn't available, Independent of user permissions.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, October 16, 2014 6:29 AM
  • Hi Praveen,

    1. There is no compatibility issue as both are using SSMS 2008 R2. But server is having 2008 R2 SP2 and person connecting is having 2008 R2.

    2. View ANY database permission to user is not available as there are other application databases running.

    Thursday, October 16, 2014 6:45 AM
  • its enterpise edition.
    Thursday, October 16, 2014 6:45 AM
  • Hi Praveen,

    1. There is no compatibility issue as both are using SSMS 2008 R2. But server is having 2008 R2 SP2 and person connecting is having 2008 R2.

    2. View ANY database permission to user is not available as there are other application databases running.

    AFAIK, DENY VIEW ANY DATABASE TO <UserName> will also hide SQL Agent, try to give VIEW ANY DATABASE permission to user and connect.

    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page


    Thursday, October 16, 2014 6:54 AM
  • Hi

    Access to mentioned roles is provided on MSDB database. Still the same issue.

    Thursday, October 16, 2014 8:11 AM
  • Hi,

    Is the user able to view other databases?


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Thursday, October 16, 2014 8:35 AM
  • There is no Enterprise Edition of SSMS. There is SSMS Basic and SSMS Complete. Prior to 2012 sp1, only SSMS Basic were available with Express Edition - but as of 2012 sp1 Expredd also offers SSMS Complete. SSMS Complete is selected bu default when you install SSMS (unless you are prior to 2012 sp1 and are using Express, of course).

    However, even SSMS Basic *should* show Agent assuming you have permissions for that. This is hearsay, but from trusted sources. Here is what to do:

    Check what is installed for the machine from where you are running SSMS. You can do that using SQL Server Installation Center - see this blog post: http://sqlblog.com/blogs/tibor_karaszi/archive/2011/02/10/what-does-this-express-edition-look-like-anyhow.aspx  (towards the end).

    On that machine try both this problematic account as well as an account which is sysadmin. Does the sysadmin account see Agent? If so, you know permissions aren't granted properly. If not, then you know the tool is the problme.

    Also try the problematic account from a machine where you know you see Agent normally. Again, this will help you assess whether the problem is the tool (SSMS) or permissions for the account.


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, October 16, 2014 11:38 AM
  • Whatif “view any database” is revoked from public to avoid listing of all user databases for normal users, but they have been granted to access msdb with “SQLAgentUserRole” to create their own jobs? Why SSMS is still protecting SQL server agent tree is protecting from such users as long as they have explicit permission on MSDB? Why not you / MSFT can provide server permission to limit/grant selective databases from listing, rather than existing either list all [ granting view any database] or none [revoke/deny view any database]? if not why do not you exclude “msdb” from “view any database” like how master & tempdb are excluded? 

    used case: Is there any fix available for SSMS to view SQL Server Agent when an user grant explicit permission in MSDB, but restricted from "view any database"? if not can you please provide such fix?

    Friday, March 18, 2016 8:23 AM
  • You would have to ask for product improvements at connect.microsoft.com. I'm sure that there already is a request to hide databases that one do not have access to, a search would pretty quick tell you that. If there is, vote for it. If not, create a new connect entry (and poast back here so we can vote).

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, March 18, 2016 8:58 AM
  • Thanks a lot for the input; now I've created a feedback / improvement request... I would request you and rest of the viewer to support the same.

    https://connect.microsoft.com/SQLServer/feedback/details/2486285

    Friday, March 18, 2016 1:39 PM
  • You would have to ask for product improvements at connect.microsoft.com. I'm sure that there already is a request to hide databases that one do not have access to, a search would pretty quick tell you that. If there is, vote for it. If not, create a new connect entry (and poast back here so we can vote).

    Indeed. Here is one, which asks for database being made securables:
    https://connect.microsoft.com/SQLServer/feedback/details/273830/need-view-definition-permissions-per-database

    It has to be done in some way like this. If SSMS would check every database for access, that could be painful on a server with many databases.

    Friday, March 18, 2016 10:24 PM
  • Thanks! good to see that you had requested this one 9 years ago but yet to get the fix or workaround from MSFT though we have got 3 to 4 versions. 
    Saturday, March 19, 2016 6:11 AM