MSSQLSM 2008 odd login failed on select statement

Question

I have a variety of users on my SQL server, all of which CAN login to the server; however one user, once logged in cannot run anything (select, alter, edit) on a specific view - but can on all the rest.

The user settings are the same as another account that has full access (seriously they are identical). For the user that cannot select etc fromt his view i get this error:

SELECT Top 1 * From View  
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'some user'.

however when i try and do this

select top 1 * from view2

i get this error:

Msg 2809, Level 18, State 1, Line 1
The request for procedure 'View2' failed because 'view2' is a view object.

but again this disappears when i remove the top 1 and works perfectly:

select * from View2

So im having three conflicting problems.. when i do a select * from every view bar the first view it works, when i do select top 1 * from those same views it doesn't work, and when i try anything (select, alter, edit) from the first view i get a login error even though i'm already logged in.

I have already tried to drop and recreate the user - no luck. I have also tried looking in the event viewer and cannot find any log messages relating to the login error.

I cannot find the security > audit folder in object explorer to check there, but when i check the management > SQL Server Logs i find nothing in there either..

Any help is much appreciated as everything i have found so far is relating to not being able to actually log into the server full stop..

Answer 1

You probably have either

• linked server issues. That is, the views access a linked server where the login mapping is incorrect

• You have 2 views in different schemas. Do you use select * from view or select * from dbo.view: are you consistent?

Answer 2

When you do a select 1 * from ViewName and you get a Login Failed, it possibly indicates one of the following:

Referencing table/view from another database:

This user may not have permissions to view objects in a different database.

Referencing table/view from Remote Server or a Linked Server:

This user may be using a mapping for either one of those servers where the login information may be out of date or may not have the proper credentials.