A 'Non admin' lost ability to modify his own SQL 2005 Jobs

Question

A 'Non admin' lost ability to modify his own SQL 2005 Jobs

Steve Verma

Old Hand

Points: 331
More actions
February 26, 2009 at 9:22 am

#155337

I have a user who is not a SysAdmin who created a couple of his own SQL Agent jobs. He has been able to modify them in the past (adding steps, changing schedule etc) but his ability to modify them has disappeared.
We have not changed his permissions so this shouldnt have happened
He is a 'dbo' of the MSDB DB and is also in the SQLAgentOperatorRole. He is still the owner of the jobs (and is also the creator). I cannot see why his ability to do this has gone. I have tried changing the owner of the jobs to somebody else and then back again (no difference), recreated the jobs from script (no difference), stopped/started services (no difference). I have removed his permissions from MSDB and regranted (no difference).
The permissions look fine to me - has anybody else come across this before?
His exact permissions within MSDB are db_ddladmin, db_dtsadmin, dbo, SQLAgentOperatorRole - this should provide what he needs (as far as i am aware)
Does anybody have any ideas?
Thanks
Steve

Viewing 5 posts - 1 through 4 (of 4 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Maybe he has some deny permission or he is a part of a role that has some deny permission? If a user has grant and deny permissions of the same option, the deny permission will be the "winner".

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Steve Verma Old Hand Points: 331 More actions · Answer 2

Thanks for the post.

I can see NO 'deny' set anywhere within the config of the system. We only have 3 DBAs here and nobody has amended the security within SQL. I can see that a couple of windows patches hit the server the night the permissions changed:

Hotfixes

KB958687

KB960715

KB890830

I cant see why a Windows patch would affect SQL permissions but this is the only thing which has changed as far as i can see - any further ideas?

subhashpunia SSC-Addicted Points: 406 More actions · Answer 3

You can check the permission of that user using sys.database_permissions and sys.server_permissions catalog views. The output of these views may help to identify the root cause.

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 4

I've seen users being granted sqlagentoperator role, also appear in SQLAgentUserRole and SQLAgentReaderRole.

Did you try (re-) adding that user to these roles ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me