Query Execution Failed for Dataset - permissions

  • r.gall

    Ten Centuries

    Points: 1030

    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:

    • Windows login called 'joebloggs'
    • 'joebloggs' is a member of the Windows Active Directory group called "OrderSystemUsers"
    • Database server is called "sql2014\production"
    • Database is called "OrderSytemDB"
    • Report is called "OrderAddressReport" which connects to a deployed DataSource called "OrderSystem_LIVE"
    • In Visual Studio, the report "OrderAddressReport" contains a dataset called "Addresses" which links to datasource "OrderSystem_LIVE" and contains the simple SELECT statement mentioned above.

     

    I am unable to enable remote errors so I am trying to figure out what the issue is.

    1. I have added the group "OrderSystemUsers"  into the "Security > Logins" folder in the Object Explorer Pane in SSMS for "sql2014\production" and in the Properties under 'server roles' have got 'public' ticked. Also, unther the 'User Mapping' section I have ticked the database "OrderSystemDB", and then in the database roles have ticked 'db_datareader', 'db_datawriter', and 'public'.
    2. In the database section of the Object Explorer (sql2014\production > Databases > OrderSystemDB > Security > Users) I can see that the Active Directory group "OrderSystemUsers" is listed, and in the properties for this under "Membership" both 'db_datareader' and 'db_datawriter' are ticked

    What could I have missed? I have tried:

    • In SSMS, going to the properties of the table the SELECT  statement is referenceing ("tbl_Address") and under 'Permissions' adding the user group "OrderSystemUsers" explicitly granting the 'Select' permission.
    • Having the user log off and on (in case permissions need to be pulled in  at log on)
    • Left it for a day in case there was an extended delay in permissions populating somewhere

    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.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22905

    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.

  • SQL_Hacker

    SSCommitted

    Points: 1648

    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/

  • r.gall

    Ten Centuries

    Points: 1030

    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?

  • Mr. Brian Gale

    SSC-Insane

    Points: 22905

    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.

  • Sue_H

    SSC Guru

    Points: 90673

    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

     

     

  • r.gall

    Ten Centuries

    Points: 1030

    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

  • Jeffrey Williams

    SSC Guru

    Points: 88532

    r.gall wrote:

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Mr. Brian Gale

    SSC-Insane

    Points: 22905

    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]
    , NULL AS [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.

    • This reply was modified 4 months, 2 weeks ago by  Mr. Brian Gale. Reason: Improved the script
    • This reply was modified 4 months, 2 weeks ago by  Mr. Brian Gale. Reason: fixed typo in SQL
    • This reply was modified 4 months, 2 weeks ago by  Mr. Brian Gale.
  • r.gall

    Ten Centuries

    Points: 1030

    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!

  • Mr. Brian Gale

    SSC-Insane

    Points: 22905

    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.

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

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