December 12, 2017 at 3:46 am
Hope this is the correct forum to post this. We are having problems with our sa user in that it SHOULD have access to everything on the server but there are some stored procedures in some databases that it cannot execute or modify whilst others in the same databases it can.
The main error is:
Msg 18456, Level 14, State 1, Line 12
Login failed for user 'sa'.
The server was a clean install and the password is not blank. Any help greatly appreciated.
December 12, 2017 at 5:09 am
Sounds like the SA account has had it's sysadmin privs revoked, and not had a user created on the database.
Out of interest, what does the following return?USE master;
SELECT sysadmin
FROM sys.syslogins
WHERE [name] = 'sa';
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2017 at 5:13 am
It returns the following:
sysadmin
1
December 12, 2017 at 5:28 am
AllenJones - Tuesday, December 12, 2017 5:13 AMIt returns the following:sysadmin
1
What are the stored procedures doing that fail compared to those that don't fail?
Are there any cross server communications happening or anything that's calling something outside of the SQL instance?
December 12, 2017 at 5:33 am
The stored procedures are updating tables in the same database but does call a linked server. Does the Linked Server user need to impersonate the sa on both servers?
December 12, 2017 at 5:48 am
AllenJones - Tuesday, December 12, 2017 5:33 AMThe stored procedures are updating tables in the same database but does call a linked server. Does the Linked Server user need to impersonate the sa on both servers?
It will depend how the linked server is setup, you can impersonate specific accounts, force one account or use pass through authentication. My guess is you have set it to impersonate the current user and the SA passwords on both machines are different (which they should be in my opinion) and thus when your executing as SA the login failures are happening due to password mismatch.
If you look on the linked servers error log you should see the 18456 error, match the serverity and state back to this link to see what is actually causing the 18456 http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
but my guess is the above.
December 12, 2017 at 6:05 am
anthony.green - Tuesday, December 12, 2017 5:48 AMMy guess is you have set it to impersonate the current user and the SA passwords on both machines are different (which they should be in my opinion) and thus when your executing as SA the login failures are happening due to password mismatch.
Totally agree here, they should be different. However, personally, I don't even think the sa account should be being used to tasks should as this. sysadmin accounts can do ANYTHING. Having them set up for impersonation could be asking for all kinds of trouble. You should be using accounts which have the correct security settings for what they need to do, and no more.
Anthony has the right plan here. Set up the impersonation between the two accounts, but I'd suggest you consider which accounts those really are. it should not be the SA.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2017 at 6:24 am
Thanks guys. I have told the devs that I will create an account for them to use and they have to stop using the sa account for their processes.
December 18, 2017 at 11:39 pm
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply