Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers

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'
share|improve this answer
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 '11 at 13:16
    
@Marian - I have edited the code above to correct the doubling. That was a typo. –  John DaCosta Feb 21 '11 at 17:58
add comment

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]

share|improve this answer
    
Thanks for the name check... I had done a replace on the actual object name. its prefixed with an alpha. –  John DaCosta Feb 18 '11 at 22:17
    
I also verified in the sys.database_permissions and the 'appuser' is configured correctly to have select permission on the view. –  John DaCosta Feb 18 '11 at 22:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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