7

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

2 Answers 2

4

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'
2
  • 1
    why would you use "like" to search if you don't really need it? Using a constant string (without any wild cards) negates the use of Like. Also, why the doubling of the condition? :-)
    – Marian
    Feb 19, 2011 at 13:16
  • @Marian - I have edited the code above to correct the doubling. That was a typo. Feb 21, 2011 at 17:58
3

You can check permissions (DENY is a permission) in sys.database_permissions

You may also have a login vs user mismatch from a restore. Look at sp_change_users_login (still useful though deprecated)

Do you have a linked server or dynamic SQL too that changes security context? Example

Finally, one thought, 1098Statement is not a valid identifier. You need to use it with delimiters [1098Statement]

2
  • Thanks for the name check... I had done a replace on the actual object name. its prefixed with an alpha. Feb 18, 2011 at 22:17
  • I also verified in the sys.database_permissions and the 'appuser' is configured correctly to have select permission on the view. Feb 18, 2011 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.