Home Forums SQL Server 2008 SQL Server Newbies The SELECT permission was denied on the object 'abc', database 'xyz', schema 'dbo' RE: The SELECT permission was denied on the object 'abc', database 'xyz', schema 'dbo'

  • 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.