The SELECT permission was denied on the object 'abc', database 'xyz', schema 'dbo'

  • Good morning Experts,

    An user is getting the below error:

    The SELECT permission was denied on the object 'abc', database 'xyz', schema 'dbo'

    I checked if he has any DENY permissions, but he does not have any. The user is mapped correctly to login with db_datareader role.Could you please advise.

  • If the login was mapped to a user with the db_reader role, you wouldn't be seeing that error.

    I assume that the query is not cross database? Can the user select from any other objects on the database?

    Thom~

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

  • Does the user have the db_datareader database role in the correct database?

    If so, has this database been restored from a different server?  If so, the SIDs of the login and user might be mismatched.  You can run this procedure in your database [abc] to get a list of them:

    EXECUTE sp_change_users_login @Action = 'report';

  • Ed Wagner - Wednesday, August 23, 2017 5:33 AM

    Does the user have the db_datareader database role in the correct database?

    If so, has this database been restored from a different server?  If so, the SIDs of the login and user might be mismatched.  You can run this procedure in your database [abc] to get a list of them:

    EXECUTE sp_change_users_login @Action = 'report';

    The user is windows user . So same SID. Also, user is not doing cross database query.

  • coolchaitu - Wednesday, August 23, 2017 5:57 AM

    The user is windows user . So same SID. Also, user is not doing cross database query.

    Then the permissions are wrong. As I asked before, can the user SELECT from any other objects on the database?

    Thom~

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

  • If the permissions were granted in the correct database, roles or group membership could play a part as well since permissions are cumulative with deny taking precedence. Check the permissions for the object 'abc', database 'xyz', schema 'dbo'  itself. Check to see if any Windows groups or roles have deny permissions. You'd also want to check if the user is in the db_denydatareader role.

    Sue

  • coolchaitu - Wednesday, August 23, 2017 5:16 AM

    Good morning Experts,

    An user is getting the below error:

    The SELECT permission was denied on the object 'abc', database 'xyz', schema 'dbo'

    I checked if he has any DENY permissions, but he does not have any. The user is mapped correctly to login with db_datareader role.Could you please advise.

    Just to be sure about the DENY permissions, can you check the results of the query below (after changing the filter to the correct username)?
    Keep in mind that DENY permissions can be set on the object level, but it is also possible to get it from a database role (like "db_denydatareader" or a self defined role).

    select db_perm.*, db_prin.name as grantee
    from sys.database_permissions db_perm
    inner join sys.database_principals db_prin
        on db_perm.grantee_principal_id = db_prin.principal_id
    where db_prin.name = 'your_user'

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • I have modified my previous query a bit. The query below displays ALL deny permissions in the database and shows to which account or role it is granted. The last OR clause can be used to include any (other) specific permissions on the object from the error message.

    select object_name(major_id) as object, db_perm.permission_name, db_perm.state_desc, db_prin.name, db_prin.type_desc
    from sys.database_permissions db_perm
    inner join sys.database_principals db_prin
        on db_perm.grantee_principal_id = db_prin.principal_id
    where state = 'D'
        or db_perm.major_id = OBJECT_ID('abc')

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • HanShi - Wednesday, August 23, 2017 8:12 AM

    I have modified my previous query a bit. The query below displays ALL deny permissions in the database and shows to which account or role it is granted. The last OR clause can be used to include any (other) specific permissions on the object from the error message.

    select object_name(major_id) as object, db_perm.permission_name, db_perm.state_desc, db_prin.name, db_prin.type_desc
    from sys.database_permissions db_perm
    inner join sys.database_principals db_prin
        on db_perm.grantee_principal_id = db_prin.principal_id
    where state = 'D'
        or db_perm.major_id = OBJECT_ID('abc')

    I have used your script and did not find any deny permission for the object.

  • I have a few thoughts on this.
    You could have a stored procedure that was created with an "EXECUTE AS" in it that might cause it to run as a different user who may not have access to the table.  
    Are you certain that the user is connected to the database you are looking at?  I know I've seen times where an application gets released with a typo in it so the program is writing data to our TEST servers instead of live.  I'd run sp_who2 to ensure that they are connected to the SQL instance you expect them to be connected to.

    If they ARE connecting to the proper instance and you are sure they are not running something as a different user (applications can be written to do impersonation as well, so it could be the application is connecting as a different user that doesn't have permissions), try running:

    USE [xyz]
    EXECUTE AS LOGIN = <username>
    GO
    SELECT USER_NAME() -- This is to verify that you are running the query as the expected login
    SELECT TOP 5 *
    FROM xyz.dbo.abc
    GO
    REVERT
    GO
    SELECT USER_NAME() -- This is to verify that you are running the query as you again

    Doing that, presuming you have impersonate permissions, will let you know if the user has access to the table.  If you don't have impersonate permissions, talk to your DBA to run that as someone with sysadmin permissions.

    TL;DR version - is the request to pull data from that table being done using some form of impersonation (SQL side or application side)?

    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.

  • bmg002 - Wednesday, August 23, 2017 10:03 AM

    I have a few thoughts on this.
    You could have a stored procedure that was created with an "EXECUTE AS" in it that might cause it to run as a different user who may not have access to the table.  
    Are you certain that the user is connected to the database you are looking at?  I know I've seen times where an application gets released with a typo in it so the program is writing data to our TEST servers instead of live.  I'd run sp_who2 to ensure that they are connected to the SQL instance you expect them to be connected to.

    If they ARE connecting to the proper instance and you are sure they are not running something as a different user (applications can be written to do impersonation as well, so it could be the application is connecting as a different user that doesn't have permissions), try running:

    USE [xyz]
    EXECUTE AS LOGIN = <username>
    GO
    SELECT USER_NAME() -- This is to verify that you are running the query as the expected login
    SELECT TOP 5 *
    FROM xyz.dbo.abc
    GO
    REVERT
    GO
    SELECT USER_NAME() -- This is to verify that you are running the query as you again

    Doing that, presuming you have impersonate permissions, will let you know if the user has access to the table.  If you don't have impersonate permissions, talk to your DBA to run that as someone with sysadmin permissions.

    TL;DR version - is the request to pull data from that table being done using some form of impersonation (SQL side or application side)?

    Thanks a lot for the script . The user is a member of several AD groups. I think one of the AD group has deny permission. Any script to find out which AD group is the culprit

  • coolchaitu - Wednesday, August 23, 2017 11:01 AM

    Thanks a lot for the script . The user is a member of several AD groups. I think one of the AD group has deny permission. Any script to find out which AD group is the culprit

    That's kind of what I thought when I posted. Check the permissions on the object itself and check the deny data reader role. You may find what group has the deny and then can work from there.

    Sue

  • Sue_H - Wednesday, August 23, 2017 11:11 AM

    coolchaitu - Wednesday, August 23, 2017 11:01 AM

    Thanks a lot for the script . The user is a member of several AD groups. I think one of the AD group has deny permission. Any script to find out which AD group is the culprit

    That's kind of what I thought when I posted. Check the permissions on the object itself and check the deny data reader role. You may find what group has the deny and then can work from there.

    Sue

    Hi Sue,
    Could you please send me a script for this

  • coolchaitu - Wednesday, August 23, 2017 11:27 AM

    Sue_H - Wednesday, August 23, 2017 11:11 AM

    coolchaitu - Wednesday, August 23, 2017 11:01 AM

    Thanks a lot for the script . The user is a member of several AD groups. I think one of the AD group has deny permission. Any script to find out which AD group is the culprit

    That's kind of what I thought when I posted. Check the permissions on the object itself and check the deny data reader role. You may find what group has the deny and then can work from there.

    Sue

    Hi Sue,
    Could you please send me a script for this

    If you look up the documentation for the database permission views, they have sample scripts. Usually you just need to add whatever user, role, object you want to filter by. You can also search the scripts up on this site.
    These are from the documentation:

    --The following query returns the members of the database roles.
    SELECT DP1.name AS DatabaseRoleName, 
     isnull (DP2.name, 'No members') AS DatabaseUserName 
    FROM sys.database_role_members AS DRM
    RIGHT OUTER JOIN sys.database_principals AS DP1
     ON DRM.role_principal_id = DP1.principal_id
    LEFT OUTER JOIN sys.database_principals AS DP2
     ON DRM.member_principal_id = DP2.principal_id
    WHERE DP1.type = 'R'
    --added the role db_denydatareader
    AND DP1.name = 'db_denydatareader'
    ORDER BY DP1.name;

    --Listing permissions on schema objects within a database
    SELECT pr.principal_id, pr.name, pr.type_desc, 
      pe.state_desc, 
      pe.permission_name, s.name + '.' + o.name AS ObjectName
    FROM sys.database_principals AS pr
    JOIN sys.database_permissions AS pe
      ON pe.grantee_principal_id = pr.principal_id
    JOIN sys.objects AS o
      ON pe.major_id = o.object_id
    JOIN sys.schemas AS s
      ON o.schema_id = s.schema_id
    --Add the object name here
    WHERE o.name = 'YourObjectName'; 

    Sue

  • here's an attempt to list all security associated to a specific login, it's AD groups, and any database roles they belong to:
    /* all permissions */
    --check for any AD groups
    CREATE TABLE #ADinfo (
        AccountName nvarchar(128),
        AccountType char(8), --user or group
        Privilege char(9), --admin, user, or null.
        MappedLogin nvarchar(128), --the mapped login name by using the mapped rules   
        PermissionPath nvarchar(128));

    INSERT INTO #ADinfo EXEC xp_logininfo N'DomainName\UserName','all';

    --lookup database roles and any permissions
    WITH member(principal_id, name) AS
       (SELECT u.principal_id, u.name
          FROM #ADinfo a
            INNER JOIN sys.database_principals u ON ISNULL(a.PermissionPath, a.MappedLogin) = u.name
        UNION ALL
        SELECT rm.role_principal_id, r.name
          FROM sys.database_role_members rm
            INNER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
            INNER JOIN member m ON rm.member_principal_id = m.principal_id)
    SELECT m.name AS user_role, p.state_desc, p.permission_name, p.class_desc,
        CASE WHEN p.class_desc = 'OBJECT_OR_COLUMN' THEN SCHEMA_NAME(o.schema_id) ELSE s.name END AS schema_name, o.name AS object_name
      FROM (SELECT DISTINCT principal_id, name FROM member) m
        LEFT OUTER JOIN sys.database_permissions p ON m.principal_id = p.grantee_principal_id
        LEFT OUTER JOIN sys.objects o ON p.major_id = o.object_id AND p.class_desc = 'OBJECT_OR_COLUMN'
        LEFT OUTER JOIN sys.schemas s ON p.major_id = s.schema_id AND p.class_desc = 'SCHEMA'
      ORDER BY s.name, o.name, m.name;

    DROP TABLE #ADinfo;
    just change the DomainName\Username in the call to xp_logininfo

Viewing 15 posts - 1 through 15 (of 26 total)

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