October 8, 2010 at 3:20 am
Hi,
when I run the following code with my sa account on SQL server 2005 sp3 x64 ENT ed
use msdb
exec sp_helpuser nonsysadmin
exec sp_helpgroup SQLAgentOperatorRole
exec as login='nonsysadmin'
use msdb
go
select user_name()
go
exec dbo.sp_start_job 'EDC test for Non sysadmin user on SE_L_01'
go
revert
I get
UserName GroupName LoginName DefDBName DefSchemaName UserID SID
---------------------- ---------------------------------------- ---------------------- ------------ ------------- ---------- -------------------------------------
nonsysadmin TargetServersRole nonsysadmin master dbo 23 0x40352E5226A4C443BC6F343DE463AB80
nonsysadmin SQLAgentOperatorRole nonsysadmin master dbo 23 0x40352E5226A4C443BC6F343DE463AB80
nonsysadmin DatabaseMailUserRole nonsysadmin master dbo 23 0x40352E5226A4C443BC6F343DE463AB80
nonsysadmin db_dtsadmin nonsysadmin master dbo 23 0x40352E5226A4C443BC6F343DE463AB80
Group_name Group_id Users_in_group Userid
------------------------- ----------- ------------------------- -----------
SQLAgentOperatorRole 8 nonsysadmin 23
(1 row(s) affected)
--------------------------------------------------------------------------------------------------------------------------------
nonsysadmin
(1 row(s) affected)
Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.
As you can see nonsysadmin user is part of SQLAgentOperatorRole role and should be able to run sp_start_job according to BOL
can someone help?
October 8, 2010 at 4:12 am
The TargetServersRole role is defined with deny execute on procedure sp_start_job and as deny overrides any grants on the procedure from all other roles, a user with TargetServersRole cannot start jobs.
exec sys.sp_helprotect @username = 'TargetServersRole', @name = 'sp_start_job'
Remove the user from the TargetServersRole and then try to start the job.
SQL = Scarcely Qualifies as a Language
October 8, 2010 at 5:55 am
thank you VERY much it is working 🙂 but now I have the following error :
Executed as user: MPADGLOBAL\EUA_SVC_SQLCMD. Warning: cannot write logfile Y:\Microsoft SQL Server\MSSQL.1\MSSQL\JOBS\EDC test for Non sysadmin user on SE_L_01.txt.
Writing to log files is only allowed to jobs that are owned by sysadmin. Please consider writing log to table. The step failed.
and I gave full access to MPADGLOBAL\EUA_SVC_SQLCMD to the JOBS directory
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply