SSMS Standard Reports Error

  • Brandie Tarvin

    SSC Guru

    Points: 172701

    I think I'm finally beginning to figure this out, but I need some assistance please. When me and my coworkers attempt to run a standard SSMS report (Disk Usage by Top Tables, etc.) in the Object Explorer / Object Explorer Details, we get the below error.

    SSMS Standard Report Error

    Almost every Google response to this error is related to SSRS (Reporting Services), not SSMS. The one link I did find recommended rebooting the server. Didn't work. I have found, however, that our server admin accounts (different from our usual logins) CAN run these reports on the server or in an SSMS window that has been opened up using that account. The difference is that our admin accounts have special login privileges on the servers while our usual login accounts are essentially locked out of direct / remote server administration permissions. Both accounts are members of the SysAdmin role on SQL Server. So that is not the issue.

    This tells me the error is related to an OS level or SAN level permission problem. The question now is, which permission?

    I tried granting Read & Execute to our normal logins on the folder where the report .dll files are located, but no joy. Still running into this error with our regular logins. Does anyone have any suggestions as to what server (not SQL Server, actual server) permissions are needed to run these reports?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thom A

    SSC Guru

    Points: 98461

    Had a bit of a Google, which led me to this topic on MSDN. According to a Microsoft Employee:

    A user with minimum right such as db_datareader will be able view standard reports if he is given the following explicit permissions

    1. View any definition
    2. view server state

    Might be worth a try, if you don't have those permissions.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Brandie Tarvin

    SSC Guru

    Points: 172701

    So those are regular windows perms, not SQL Server perms?

    I thought those were SQL Perms.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Sue_H

    SSC Guru

    Points: 90287

    Those are SQL Server permissions. It has nothing to do with Windows level permissions. They are just reports viewed through the report viewer control. The permissions to the data source would be needed by whoever is running the reports. The reports select from the DMVs (and other things but mostly DMVs) that the user running the reports would need access to.

    Sue

  • Brandie Tarvin

    SSC Guru

    Points: 172701

    Well, I'm sysadmin on the server and I'm trying to run the reports. I can only succeed at running the reports if I'm logged in with a separate sysadmin account that has full administrative rights (windows) on the server. It fails every time I run it with my non-admin account even though that account has full admin rights (sql) on the server.

    That has convinced me there is a windows permission or three that is relevant to running the reports that I seem to be missing for my non-admin account. If anyone has any thoughts to windows level permissions, I'd appreciate it.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716661

    That's interesting. I wonder if these reports are using your account as a proxy for some WMI call? I don't know, but asking MS if they can comment.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716661

    Two ideas here, but first, was SSMS installed with a different account than yours?

     

    1. Your account lacks access to the RDL files used by SSMS
    2. Your account lacks access to the temp folder that the RDL files use (use Sysinternals to track this down) I have no idea where this is, but suggested by someone.
  • frederico_fonseca

    SSChampion

    Points: 14233

    If you can get procmon from sysinternals suite https://docs.microsoft.com/en-us/sysinternals/downloads/sysinternals-suite] and within the server itself kick it off and monitor both SQL Server and SSMS and see if it gets a "access denied" to some file/folder/registry entry

     

    PS: Sysinternals is a must have tool to track issues - for example those with SPN double hop could "easily" identify lack of access to the local SQL Server user temp folder as the culprit of a double hop failure.

    and great for other things 🙂 - I use the ADExplorer a lot

  • Brandie Tarvin

    SSC Guru

    Points: 172701

    Steve Jones - SSC Editor wrote:

    Two ideas here, but first, was SSMS installed with a different account than yours?

    1. Your account lacks access to the RDL files used by SSMS
    2. Your account lacks access to the temp folder that the RDL files use (use Sysinternals to track this down) I have no idea where this is, but suggested by someone.

    #2 sounds about right to me. I don't know if it was me or my coworker who installed SQL on this server, but we did it with our server admin accounts, not our regular accounts, and as I said previously, corporate locked our regular accounts completely out of the server. So folder access issues make sense to me. I'll see if I can locate them and verify my access.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin

    SSC Guru

    Points: 172701

    frederico_fonseca wrote:

    If you can get procmon from sysinternals suite https://docs.microsoft.com/en-us/sysinternals/downloads/sysinternals-suite] and within the server itself kick it off and monitor both SQL Server and SSMS and see if it gets a "access denied" to some file/folder/registry entry   PS: Sysinternals is a must have tool to track issues - for example those with SPN double hop could "easily" identify lack of access to the local SQL Server user temp folder as the culprit of a double hop failure. and great for other things 🙂 - I use the ADExplorer a lot

    Thanks. I'll look into this.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88174

    I think you are looking in the wrong place - SSMS does not access files on the server to run any reports.  You need to look at permissions on your non-admin account on your workstation (which I assume is the account you are using).

    This problem appears to be related to which domain account is logged on - regardless of which machine.  So - windows permissions for the non-admin account do not have the necessary privileges to run the SSMS reports (again - on either the server or a workstation) but your admin account does have permissions.

    To test further - you can temporarily add your non-admin account to the local administrators group.  Logon to that device using your admin account, add the non-admin account to local administrators, logon with the 'non-admin' account - and see if you can now run the reports.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Sue_H

    SSC Guru

    Points: 90287

    Jeffrey Williams 3188 wrote:

    I think you are looking in the wrong place - SSMS does not access files on the server to run any reports.  You need to look at permissions on your non-admin account on your workstation (which I assume is the account you are using). This problem appears to be related to which domain account is logged on - regardless of which machine.  So - windows permissions for the non-admin account do not have the necessary privileges to run the SSMS reports (again - on either the server or a workstation) but your admin account does have permissions. To test further - you can temporarily add your non-admin account to the local administrators group.  Logon to that device using your admin account, add the non-admin account to local administrators, logon with the 'non-admin' account - and see if you can now run the reports.

    + This. It's local. Other scenarios could come into play such as using RunAs with SSMS on their PCs. Nonetheless, the permissions denied would show up with Process Monitor but I would run it on the PC, not on the server. It would show which login was trying to access the files as well.

    Sue

  • Brandie Tarvin

    SSC Guru

    Points: 172701

    No, it's not local. My administrative account has less permissions on my workstation than my non-administrative account. My non-administrative account can elevate its permissions up to local administrator. My administrative account can't. Both accounts have the exact same SQL Server permissions, sysadmin.

    I've been working on this issue on and off for over a year now. It finally got to the point where I ran out of things to check. But I'll know soon. I need to finish working an issue with AGs and encrypted databases then I can test the reports issue.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 13 posts - 1 through 13 (of 13 total)

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