Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dundealing
Starting Member
12 Posts |
 Posted - 2007-01-30 : 09:06:24
|
| Hi guys,I am new to this forum and new to SQL.I have a stored procedure which sets the value of a variable (@owner_logon_name, type sysname) to sa. It then calls sp_add_job in the msdb database, which in turn calls numerous other stored procedures. The ASP page I post down to the server is coming back with an internal server error and the log file shows the following message:"Only a system administrator can reassign ownership of a job."Even though the login name was set to sa, it seems to be using another role. The code I have in my sp is:exec @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output , @job_name = @JobName, @owner_login_name = sa, @description etc, etc Can anyone think why it would do this? An explanation of how sp_add_job works would also be much apprciated. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-01-30 : 11:57:16
|
You are getting this error because you are executing it as a login that does not have permission.from BOL - http://msdn2.microsoft.com/en-us/library/ms182079.aspxquote:
PermissionsTo run this stored procedure, users must be a member of the sysadmin fixed server role, or be granted one of the following SQL Server Agent fixed database roles, which reside in the msdb database: * SQLAgentUserRole * SQLAgentReaderRole * SQLAgentOperatorRoleFor information about the specific permissions that are associated with each of these fixed database roles, see SQL Server Agent Fixed Database Roles.
EDIT: btw, it is not a good idea to give IIS these kinds of permissions. if you do, it opens up your page to all sorts of attacks. If you really need to execute this proc from an asp, I would use impersonation so that the NT credentials of the user on the page are used. if that nt account has permissions to administer jobs, then the call will work. finally, I am guessing that you are building some sort of web based admin app for sql server. if so, you might want to check this out: http://www.microsoft.com/downloads/details.aspx?FamilyId=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en
www.elsasoft.org |
 |
|
|
|
|
|
|
|
Only a system administrator can reassign ownership