March 30, 2012 at 8:38 am
I scheduled a brand new job that executes a sp on the database. The job errored out with a message
Message
'EXECUTE AS USER' failed for the requested user 'xyzINC\SVC_SQL' in the database 'mydatabase. The step failed.
Then in the job properties in the advanced tab I removed the 'xyzINC\SVC_SQL' user in the run as user option and it worked. Can somebody explain why it worked when I removed SVC_SQL from the run as user?
Any pointers will be greatl appreciated. Thanks
March 30, 2012 at 9:34 am
That user doesn't appear to have permissions on the proc being called from the job.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2012 at 9:36 am
SQLRNNR (3/30/2012)
That user doesn't appear to have permissions on the proc being called from the job.
I thought so and did grant Execute permission but still got the same error.
March 30, 2012 at 9:37 am
Can you run that proc as that user from within a query window?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2012 at 10:05 am
SQLRNNR (3/30/2012)
Can you run that proc as that user from within a query window?
I tried that..got the following error
The server principal "xyzINC\SVC_SQL" is not able to access the database "mydatabase" under the current security context.
March 30, 2012 at 10:11 am
Check if the sqllogin is orphaned.
March 30, 2012 at 10:22 am
SS999 (3/30/2012)
Check if the sqllogin is orphaned.
Checked. It is not.
March 30, 2012 at 10:41 am
Make sure the job u created is running under agent service account and provide execute as permission to the user if not restricted grant control as well and see.
March 30, 2012 at 10:51 am
Have you exclusively granted CONNECT to that security login for the DB
Ex:
USE [mydatabase]
GO
CREATE USER [xyzINC\SVC_SQL] FOR LOGIN [xyzINC\SVC_SQL]
GO
GRANT CONNECT TO [xyzINC\SVC_SQL]
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply