November 30, 2012 at 2:17 pm
I’m seeing the following error message when I try to query seven specific views in a database: Msg 18456, Level 14, State 1, Line 1 Login failed for user ‘domain\user.’ This is happening to certain local SQL accounts as well as certain domain accounts. Other local and domain accounts can access these views just fine. I can’t seem to find any reason as to which accounts can/can’t access these views. For instance I have two domain accounts with identical security configurations, one gets the error and the other does not. This error is even occurring with the SA account and other users with the sysadmin role. All of the accounts in question have proper rights to read the DB. I made sure there aren’t any explicit deny permissions. I tried adding explicit grant SELECT permissions on the views but it didn’t make any difference. I’m using remote desktop to connect to the 2008 R2 SP1 SQL server and using management studio (SSMS) to run these queries so there isn’t a connection string to check. There aren’t any linked servers or cross-DB queries involved; each of these problematic views only references data contained in the same database. Using an account that gets the error on the views, I can directly access the data in the tables referenced by the view and I can access other views just fine. Any insight as to why this is happening and how to fix it is greatly appreciated.
November 30, 2012 at 2:46 pm
crowellc (11/30/2012)
I’m seeing the following error message when I try to query seven specific views in a database: Msg 18456, Level 14, State 1, Line 1 Login failed for user ‘domain\user.’ This is happening to certain local SQL accounts as well as certain domain accounts. Other local and domain accounts can access these views just fine. I can’t seem to find any reason as to which accounts can/can’t access these views. For instance I have two domain accounts with identical security configurations, one gets the error and the other does not. This error is even occurring with the SA account and other users with the sysadmin role. All of the accounts in question have proper rights to read the DB. I made sure there aren’t any explicit deny permissions. I tried adding explicit grant SELECT permissions on the views but it didn’t make any difference. I’m using remote desktop to connect to the 2008 R2 SP1 SQL server and using management studio (SSMS) to run these queries so there isn’t a connection string to check. There aren’t any linked servers or cross-DB queries involved; each of these problematic views only references data contained in the same database. Using an account that gets the error on the views, I can directly access the data in the tables referenced by the view and I can access other views just fine. Any insight as to why this is happening and how to fix it is greatly appreciated.
wow you seemed to have looked at everything so far;
the wierd part is the Login failed error; that clearly seems to be a linked server to me, rather than permissions in an existing object.
can you check master.sys.synonyms and yourdatabase.sys.synonyms for something pointint to a linked server table? it might be that a view SEEMS to be selecting from a table, but goes to teh linked server via synonym instead?
can you do something like this and check if it's a real login error in SSMS:
EXECUTE AS USER='domain\UserWithProblem'
select * from OffendingView
REVERT; --change back into myself
does that give a login error?
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply