I suspect I have a permissions issue that I cannot track down the source of. My report runs for me fine in Visual Studio so I know it should work, but when deployed, the people using it can't run it and get the error:
"An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'Addresses'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors"
I have an SSRS report that gets its data by running a simple SELECT statement directly in the report (as opposed to running a stored procedure). The SQL is so simple I didn't see it necessary to create a stored procedure as its along the lines of "SELECT fldAddressLine1, fldAddressline2, fldCity, fldPostcode FROM tbl_Address"
The setup is:
I am unable to enable remote errors so I am trying to figure out what the issue is.
What could I have missed? I have tried:
I need the user 'joebloggs' to be in the Active Directory group 'OrderSystemUsers' so that I can add more users in future without having to grant permissions over and over.
February 13, 2020 at 8:26 pm
First, did you try what was suggested in the error:
navigate to the report server on the local server machine
Based on what you have tried, my thought on this is it might not be a permission related error, or at least not a permission error with the user "jobebloggs" or the AD group "OrderSystemUsers".
Since it works in visual studio, my guess is it is a data source configuration error. The VERY first thing I would check is if the report you uploaded HAS a data source associated with it. It might in VS, but if that data source didn't exist in SSRS, it will map to nothing and need to be manually mapped.
Once you are certain the data source is correct, Check what user the data source connect to the database as. If it is connecting as a specific user, that user would need permissions at the database level. If it is passing the credentials along from the user viewing the report, do you have other reports that do this where it is successful?
But before you can do much troubleshooting, you need to know the error. Blindly assuming it is a permission based error may lead you down a bunch of rabbit holes only to discover you are looking at the wrong thing.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 13, 2020 at 8:35 pm
I agree with Brian Gale, you need to go to the local server. You may need to add the AD group to the Security page in the Reports web screen. What version of SSRS are you running? If you open Reporting Services Configuration on the server, go to the Web Portal and then click on the top link (assuming it's not encrypted) and that will take you to the home page of SSRS. Then you need to get to the Security page and add the AD group to have at least Browser permission so the users can view the report.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 14, 2020 at 11:45 am
Thanks Brian.
Since checking the AG was set against the datasource and dataset correctly, my Administrator has been able to enable remote errors for me this morning and I now have a meaningful error stating that the SELECT permission was denied:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'Addresses'. (rsErrorExecutingCommand)
The SELECT permission was denied on the object 'vwOrders', database 'OrderSystemDB', schema 'dbo'.
I didn't include the full version of the original SELECT query, and it actually contains a sub-query in the list of columns selected referencing this view.
Are the roles "db_datareader" and "db_datawriter" be only sufficient for selecting from tables, and not views? Do I instead need to explicitly grant the SELECT permission on the View object?
February 14, 2020 at 3:02 pm
Granting db_datareader should be all that is needed to have permissions on the view unless you have explicit DENY permissions on the view for a group that user is a member of or for that user directly. If I remember SQL Server permissions correctly, DENY takes precedence over GRANT. If the user doesn't have an explicit "DENY" on the view, you will need to dig deeper.
If it isn't an explicit DENY, it sounds like your data source is set to connect as a specific user or isn't passing the correct user along and thus the permissions you are setting are not for the correct user. My next step would be to load up profiler (or extended events aka XE) and have a look at who the code is running as. Once you know this, you can adjust permissions appropriately.
Something I'd be careful of though is granting db_datawriter permissions. Depending on the data in the database, I'd be careful of db_datareader too. If any confidential data exists in the database, db_datareader is a bit of a security risk. db_datawriter is a HUGE security risk on a live system as they can write to any table. I would strongly recommend following a "least permissions" model and use the data reader and data writer roles sparingly. It may be fine for a test system or during initial setup while testing something, kind of like turning off a firewall or granting "EVERYONE" full control on a folder, but I try not to leave those things set that way long term.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 14, 2020 at 3:46 pm
Yup, deny takes precedence with the only exception being column level permissions and table denies - which is suppose to be deprecated at some point. A member of a role or being in a group with deny could also impact this. With nested groups and such, sometimes it can be faster to just look for the denies to figure out if the user is impacted from that. There is a script up here on SSC that you could use to check for denies:
Find Deny permissions on database objects
Sue
February 17, 2020 at 10:19 am
Hi Sue, I have tried the script you linked to, thanks. However it does not return anything that was created for our database, instead it returns items that appear to be general ones that you get when you create a new database:
grantee_name = guest, state_desc = DENY, permission_name = EXECUTE
object names:
sp_helpdiagrams
sp_helpdiagramdefinition
sp_creatediagram
sp_renamediagram
sp_alterdiagram
sp_dropdiagram
fn_diagramobjects
February 17, 2020 at 7:14 pm
Thanks Brian.
Since checking the AG was set against the datasource and dataset correctly, my Administrator has been able to enable remote errors for me this morning and I now have a meaningful error stating that the SELECT permission was denied:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'Addresses'. (rsErrorExecutingCommand)
The SELECT permission was denied on the object 'vwOrders', database 'OrderSystemDB', schema 'dbo'.
I didn't include the full version of the original SELECT query, and it actually contains a sub-query in the list of columns selected referencing this view.
Are the roles "db_datareader" and "db_datawriter" be only sufficient for selecting from tables, and not views? Do I instead need to explicitly grant the SELECT permission on the View object?
Is that view in the same database where you are granting permissions - or is it in a different database? In the code that calls this view - are you doing something like this?
SELECT ...
FROM ...
JOIN OrderSystemDB.dbo.vwOrders
WHERE ...
If so - then you need to grant permissions in the database OrderSystemDB for the login/user that is connecting from your report.
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
Did you run profiler/extended events to see which account your query was being run as?
Also, is this a cross database query OR does the view contain cross-database objects?
What I mean by a cross database query is does your Data Source connect to database ABC and your query is using the database OrderSystemDB? If so, did you set the db_datareader permission on database ABC or OrderSystemDB? It would need to be on OrderSystemDB, but you would also need connect permissions on database ABC.
You can use this query to check all database permissions granted to a user/group:
USE <databasename>
DECLARE @userName VARCHAR(255) = NULL;
DECLARE @tmpTable TABLE
(
[account name] VARCHAR(255)
, [type] VARCHAR(255)
, [privilege] VARCHAR(255)
, [mapped login name] VARCHAR(255)
, [permission path] VARCHAR(255)
);
DECLARE @groupName VARCHAR(255);
DECLARE [curse] CURSOR LOCAL FAST_FORWARD FOR
SELECT
[name]
FROM[sys].[database_principals]
WHERE[type_desc] = 'windows_group';
OPEN [curse];
FETCH NEXT FROM [curse]
INTO
@groupName;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tmpTable
EXEC [sys].[xp_logininfo]
@acctname = @groupName
, @option = 'members';
FETCH NEXT FROM [curse]
INTO
@groupName;
END;
CLOSE [curse];
DEALLOCATE [curse];
SELECT
[princ].[name]
, NULLAS [Role]
, [princ].[type_desc]
, [perm].[permission_name]
, [perm].[state_desc]
, [perm].[class_desc]
, OBJECT_NAME([perm].[major_id]) AS [Object]
FROM[sys].[database_principals] AS [princ]
LEFT JOIN[sys].[database_permissions] AS [perm]
ON [perm].[grantee_principal_id] = [princ].[principal_id]
JOIN[sys].[sysusers] AS [sysuser]
ON [princ].[principal_id] = [sysuser].[uid]
AND
(
[sysuser].[hasdbaccess] = 1
OR[sysuser].[issqlrole] = 1
)
WHERE[princ].[name] IN
(
SELECT
[permission path]
FROM@tmpTable
WHERE[mapped login name] = @userName
)
OR[princ].[name] = @userName
UNION
SELECT
[memberprinc].[name]AS [name]
, [roleprinc].[name]AS [role]
, [memberprinc].[type_desc]
, [perm].[permission_name]
, [perm].[state_desc]
, [perm].[class_desc]
, OBJECT_NAME([perm].[major_id]) AS [Object]
FROM[sys].[database_role_members] AS [members]
JOIN[sys].[database_principals] AS [roleprinc]
ON [roleprinc].[principal_id] = [members].[role_principal_id]
JOIN[sys].[database_principals] AS [memberprinc]
ON [memberprinc].[principal_id] = [members].[member_principal_id]
LEFT JOIN[sys].[database_permissions] AS [perm]
ON [perm].[grantee_principal_id] = [memberprinc].[principal_id]
JOIN[sys].[sysusers] AS [sysuser]
ON [memberprinc].[principal_id] = [sysuser].[uid]
AND
(
[sysuser].[hasdbaccess] = 1
OR[sysuser].[issqlrole] = 1
)
WHERE[memberprinc].[name] IN
(
SELECT
[permission path]
FROM@tmpTable
WHERE[mapped login name] = @userName
)
OR[memberprinc].[name] = @userName
ORDER BY[princ].[name];
It will give you the users, groups and their permissions. You can filter on a single username or group name by changing the "NULL" to be the name of the user/group.
Using this query, you can determine who has what permissions and maybe see where things are misbehaving. Only thing it won't tell you is a user inside of a windows group. For example, if user A is a member of group B and group B was granted permissions, the above script may not give you the results you expect when looking at user A. BUT that last stored procedure (xp_logininfo) will look at the userName you gave as a parameter and show you the members of the group so you can verify that user A is actually a member of group B.
Above script was taken from the link provided by Sue_H and modified to show more information.
EDIT - Updated the SQL script so it will look for the user inside of a group. So if user A is inside group B, it will search for the user and the group permissions.
EDIT2 - updated the filter to be NULL instead of 'public' so rest of this post made sense.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 18, 2020 at 4:07 pm
Mr Brian Gale - you asked if this was a cross-database query, and that's exactly what it turned out to be, thank you. I needed to grant datareader permissions in the second database that was referenced in the view. I had not noticed that until you suggested it.
Your script only partially worked for me, it produced error code 0x534:
"(8 row(s) affected)
(36 row(s) affected)
Msg 15404, Level 16, State 3, Procedure xp_logininfo, Line 43
Could not obtain information about Windows NT group/user 'xxxxxxxxxxxx', error code 0x534"
However the group/user it returned in this error was the exact group I needed to grant datareader permissions to in the other database. Thank you!
February 18, 2020 at 6:48 pm
That error is strange. It should be executing the stored procedure xp_logininfo against only Windows AD Groups. If it got an error, I'm guessing you don't have permission to look at the group members. Guess my script needs a tweak...
Either way, glad it is working for you.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply