December 14, 2009 at 8:48 am
I have been using this job on servers for years, though I had not done much with SQL2008 servers. Now that I am deploying SQL2008 servers I have noticed that my job no longer works, and I cannot tell why, from the limited log info:
Message
Executed as user: dbo. User does not have permission to perform this action. [SQLSTATE 42000] (Error 15247). The step failed.
The job runs under the SQL Agent, which is the same user as SQL Server runs under, just like on my SQL2005 boxes, but I get the above error on SQL2008. I run the following script on 2005 and it works perfectly.
Note: I am not saying this script does not run on SQL 2008, I am saying the resulting job is creates will not run, giving the error mentioned about. The job runs fine on 2005.
I've checked all rights, and can't see a way to give the user any MORE rights on the server.
The job script:
-- Script generated on 12-6-2004 3:22 PM
-- By: Chris Stamey
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'CycleErrorlog')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''CycleErrorlog'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'CycleErrorlog'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'CycleErrorlog', @owner_login_name = N'sa', @description = N'Cycle the Errorlog. Keeps the Errorlog from getting to large to review. Chris Stamey', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 2, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'CycleErrorlog', @command = N'sp_cycle_errorlog', @database_name = N'master', @server = N'', @database_user_name = N'dbo', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = N'Sunday0001',
@enabled = 1,
@freq_type = 8,
@active_start_date = 20040317,
@active_start_time = 000100,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 1,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10
GO
Am I nmissing some fundamental difference between 2005 and 2008?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
December 14, 2009 at 9:15 am
Do you have a proxy set for the job? Or can you execute each statement with the SETUSER clause and see which one might be causing issues? My guess is it's permissions related, which might change for jobs/service accounts from version to version
December 14, 2009 at 11:58 am
Oh so curious behavior.
Perhaps I'm not holding my mouth right:
Use Master
Go
Exec ('exec sp_cycle_errorlog')
As user = 'dbo';
As login = 'sa';
Msg 15247, Level 16, State 1, Procedure sp_cycle_errorlog, Line 5
User does not have permission to perform this action.
User Master
Go
Exec ('exec sp_cycle_errorlog')
As login = 'sa';
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This works the same way on SQL2005 and SQL2008, so isn't a valid test of the problem.
The job is owned by SA and says it is executed as "dbo". No proxies set up anywhere, on SQL2005 or 2008.
Not sure where to go from here.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
December 22, 2009 at 7:10 am
Found the problem, although I don't necessarily understand yet why it happened. The job step had "dbo" in the "Run As User" text box in Advanced properties of the job step. This is different from the "Run As" list box in the General Page of job step properties. In my script it is "@database_user_name = N'dbo'", which I removed and it works just like in 2005 now. In 2005 this entry, "Run As User", is filled with "dbo" and the job runs just fine.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply