Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Securitysql-server-2005

I have a database with a View Named '1098Statement'
We are doing testing for an application with user name 'appuser'.

I have tried the following solutions to granting select on the view and keep getting error:

  1. Grant Select on View
  2. Add user to db_datareader

  3. Add user to a database role which has select granted on the view

    Msg 229, Level 14, State 5, Line 2
    The SELECT permission was denied on the object '1098Statement', database 'mydb', schema 'dbo'.

None of them work.

Are there any pointers anyone can provide on troubleshooting this issue.

My only remaining thoughts are to check

  1. the user account to see if there are any explicit "deny" restrictions on either the user or any roles the account belongs to.
  2. check the view to see if there are any explicit deny

Best Answer

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user. So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT 
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc] 
,dp.default_schema_name as [database_principal_default_schema_name]
FROM 
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE 
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'
Related Question