Post reply

sql 2000 server agent db user permissions problem

  • September 24, 2010 at 11:51 am

    #142856

    Hope someone can help me. I have the following problem:

    I use SQL Server 2000 sp4 on Windows 2003 Server. I have a user which has no server role rights, only public, datareader, datawriter and ddl rights to a user database (none of the system databases).

    In SQL Studio Manager the user is able to create and delete his own tasks in the SQL Server Agent and view them.

    In the only way to stop the user from creating and deleting is by adding deny datareader and deny datawriter in the msdb database.

    From others posts, I believe he shouldn't be able to create, delete and view the task. Can someone help or explain?

  • September 24, 2010 at 12:43 pm

    #1227174

    Though I think you'd need to test this you could perhaps Add an explicit deny to execute sp_add_job for the particular user you are having issues with.

    sp_add_job is what is used to add a new job, the same settings would need to be applied to sp_delete_job, sp_start_job, sp_stop_job, etc.

    Also make certain you document what you did so you can undo it if the need arises.

    Also, if you need business justifications for an upgrade, managing SQL agent job security is much easier in 2005 and above, not withstanding the whole SQL 2k isn't supported any more argument of course 😉

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply