October 19, 2009 at 5:38 pm
Hello all, been poking around with this for a couple of days and just wanted to see if anyone knows if this is a bug.
My issue is this, I have an automated script that I use to create jobs. In the job steps themselves we set the "Database_User_Name" to a windows NT account that has full rights on the actual server as well is a sysadmin on the sql server. In 2005 when I run the job there is no issue. When I run it in sql 2008 I keep getting "User does not have permission to perform this action. [SQLSTATE 42000] (Error 15247) Adding linked server. " I am just trying to add a simple linked server.
Now to resolve this I was able to go to msdb.dbo.sysjobsteps table and update the "database_user_name" field to "Null" and am now able to run the job.
As usual I started with books on line to see if anything had changed with sp_add_jobstep, I see that nothing has changed for the database_user_name: "[ @database_user_name = ] 'user'
The name of the user account to use when executing a Transact-SQL step. user is sysname, with a default of NULL. When user is NULL, the step runs in the job owner's user context on database."
What is odd is the owner and the database_user_name are both the same windows NT account.
Anyone else having this issue or am I just missing something? Thanks.
August 16, 2012 at 1:20 pm
I'm having a similar issue. I have a script which uses sp_add_jobstep to add a job step and historically I have always set the database_user_name to 'dbo' when calling this proc. Prior to SQL Server 2008 there has never been an issue with this but since SQL Server 2008, some jobs created this way seem to fail with the same error message you detailed. I have only been able to fix it by removing dbo from the database_user_name and leaving it blank. Seems to me this is a bug though?..
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply