They haven't permission to touch even the sql server agent without removing sa priviledge.

Question

They haven't permission to touch even the sql server agent without removing sa priviledge.

forsqlserver

SSCoach

Points: 18902
More actions
September 22, 2011 at 12:46 am

#243440

Hi,
The developers have sa priviledge on 1 server , they usually stop the sqlserveragent service and due to which the nightly backup jobs fail.
Can it be possible to stop the developers that they haven't permission to touch even the sql server agent without removing sa priviledge.
Or
Is there any other solution?
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

if they have sa permission then there is not much you can do, and anything you did do they could undo.

best solution is to assign the developers the correct permissions to do their role as developers, using sa is bad practice even in dev.

other option is to tell them to stop messing with the sql agent.

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

steveb. (9/22/2011)
other option is to tell them to stop messing with the sql agent.

Agreed. It's not always a matter of tools. Appropriate communication an cooperation can do more than permissions at times.

Try to involve them in the backup process and explain why stopping the agent is bad.

However, you should try to revoke sysdmin permissions.

-- Gianluca Sartori

forsqlserver SSCoach Points: 18902 More actions · Answer 3

Is it not possible from the proxy account of sql server?

Thanks

steveb. SSC-Forever Points: 46733 More actions · Answer 4

forsqlserver (9/22/2011)
Is it not possible from the proxy account of sql server?

Seriously do yourself a big favour, and sort out the correct permissions for the developers and forget about implementing other measures. There are roles that give a lot of access without giving over full control

The devs may complain but it is your responsibilty to administer the database (i assume you are the dba). Because eventually one of them is going to drop and delete a database or bring the server down and it will be over to you to sort it.

Also communication is the key, let them know what the issues are and why they should not have sa access.

forsqlserver SSCoach Points: 18902 More actions · Answer 5

Thanks,

Actually their applications do not installing sa priviledge.

Thanks

forsqlserver SSCoach Points: 18902 More actions · Answer 6

Thanks,

Actually their applications SFS does not installing without sa priviledge.

Thanks

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

forsqlserver (9/22/2011)
Thanks,
Actually their applications SFS does not installing without sa priviledge.

Probably because they're not aware of the single privileges that they have to be granted instead of sysadmin.

If they started coding with restricted users, you could have granted single privileges. Now it would be a PITA to find them all.

Another good reason to avoid granting sysadmin to devs.

-- Gianluca Sartori

forsqlserver SSCoach Points: 18902 More actions · Answer 8

Probably because they're not aware of the single privileges that they have to be granted instead of sysadmin.

If they started coding with restricted users, you could have granted single privileges. Now it would be a PITA to find them all.

Another good reason to avoid granting sysadmin to devs.

Thanks Sartori,But....

1. What is PITA?

2. How it can be possible with single priviledge login?Please tell actually this is devleopemnt and our production is about to start.So then I cannot give permission of sa?

3. I have forefront,TFS,HRMS Axapta all these developers have taken sa rights perforcely from me.I have not solid reason for this.

Thanks

steveb. SSC-Forever Points: 46733 More actions · Answer 9

forsqlserver (9/22/2011)
Thanks,
Actually their applications SFS does not installing without sa priviledge.

Then it is a poorly developed application,

find out exactly what the applicaiton needs to do with the SQL server and then give the appropriate permissions based upon need. Otherwise you end up with lazy devs, as I can bet they do not NEED sa permission.,

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

1. What is PITA?

Pain In The A$$. I didn't want to write it fully, I though the acronym would have been enough.

2. How it can be possible with single priviledge login?Please tell actually this is devleopemnt and our production is about to start.So then I cannot give permission of sa?

You should have done this from the start, now I'm afraid it's too late. Especially if you want to do it in a rush.

3. I have forefront,TFS,HRMS Axapta all these developers have taken sa rights perforcely from me.I have not solid reason for this.

You're right: that's the reason why here I still have some devs with sysadmin rights. They shouldn't, but this is the best I could do.

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 11

forsqlserver (9/22/2011)
Hi,
The developers have sa priviledge on 1 server , they usually stop the sqlserveragent service and due to which the nightly backup jobs fail.
Can it be possible to stop the developers that they haven't permission to touch even the sql server agent without removing sa priviledge.
Or
Is there any other solution?

in a similar situation, I know another poster ended up creating a Windows scheduled task that ran the "net start SQLSERVERAGENT"

to clean up after his developers, exactly so that the backups would not fail, because they were stopping the service.

in his case, the developers did not know how to kick people off of a database (using KILL @spid or ALTER DATABASE SET SINGLE USER ROLLBACK IMMEDIATE), so they would bounce the dev server to kick people off.

he could not get them trained and just gave up and did the scheduled windows task as a work around.

and he c

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

didier41 Say Hey Kid Points: 685 More actions · Answer 12

not to mention, developers logging in with application logins. Like mentioned above, I've seen applications that need sysadmin rights. So are the joys of life...