SSMS Standard Reports Error
-
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.
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?
-
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
- View any definition
- 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.
Larnu.uk -
So those are regular windows perms, not SQL Server perms?
I thought those were SQL Perms.
-
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
-
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.
-
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.
-
Two ideas here, but first, was SSMS installed with a different account than yours?
- Your account lacks access to the RDL files used by SSMS
- 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.
-
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
- Steve Jones - SSC Editor wrote:
Two ideas here, but first, was SSMS installed with a different account than yours?
- Your account lacks access to the RDL files used by SSMS
- 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.
- 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.
-
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.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams 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
-
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.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply