What privs do you have on SQL Server itself? Do you have "SA" privs? If not, you're pretty much stuck. If you do, then it may be that you have to start SSMS a little differently. I keep an icon on my desktop to start SSMS. If hold down the {Shift} key and right click on the icon, you should see a selection of "Run as Administrator". Click on that and see if you now have control over the agent from SSMS.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

dwilliscp (12/31/2013)Is there anyway to stop and start the agent, if you are not a local/network admin? As a DBA IT will not give me local or network admin permissions.

Does the account under which SQL Server runs have machine admin permissions? If so, then you could try enabling and using xp_cmdshell to run NET START for the Agent service (please disable xp_cmdshell as soon as you do this; it is a huge security hole). However, you must have sa permissions on the SQL server to do so.

However, the above is a lousy solution. There is no reasonable grounds for not allowing DBA to be machine admin on any SQL Server instance. In fact, I'd argue that such rights are vital to the job of the production DBA.

JohnFTamburo (12/31/2013)(please disable xp_cmdshell as soon as you do this; it is a huge security hole)

No it's not. Poor ol' xp_CmdShell gets blamed for other very poor security practices a lot. It's not actually a security risk. Bad security is a security risk.

Stop and think about it. Who can use xp_CmdShell? If you've done it right, only people who have "SA" privs can use it. Who can turn it on or off? Right again... only those folks that have "SA" or "Control Server" privs.

If an attacker breaks in as a non-SA prived user, can he use xp_CmdShell if even if it's enabled? No. If an attacker breaks in as an SA prived user, can he use xp_CmdShell even if it's disabled? Since he also has the privs to enable it, the answer is "Yes". Even if xp_CmdShell could be permanently disabled, an unauthorized person getting into your system can still get to other systems using the command block of a job, PowerShell, and SQLCmd. The REAL security risk isn't having xp_CmdShell enabled... the real security risk is allowing unauthorized people in as "SA".

What about DBAs using it to elevate their privs as you have just done? It's not the usage of xp_CmdShell that caused that risk. It's the fact that you think DBAs have to have "SA" privs to do their job.

"DBA" is an abbreviation for "Database Administrator"... not "System Administrator". If your DBAs have SA privs and you don't actually want them to be a "security risk", then give them the correct privs for the job instead of blaming xp_CmdShell.

What about DBAs using it to elevate their privs as you have just done? It's not the usage of xp_CmdShell that caused that risk. It's the fact that you think DBAs have to have "SA" privs to do their job.

"DBA" is an abbreviation for "Database Administrator"... not "System Administrator". If your DBAs have SA privs and you don't actually want them to be a "security risk", then give them the correct privs for the job instead of blaming xp_CmdShell.

With respect to the proper level of privileges to grant to a production DBA, I agree to disagree. A DBA should have the rights necessary to do her or his job. Generally that requires SA level privileges for the production level DBA, not for the world. The question then becomes: do you trust your DBA? In the case of the original poster of this thread, the poor guy cannot even start the SQL Server agent without jumping through hoops. This seems to me to be excessive control for its own sake, not prudent access management.

I've been in environments where connection strings explicitly log an application in as sa. Luckily, where I am now, that is no longer the case. A DBA's job sometime requires that person to stop and start services, etc. Machine admin is not unreasonable.

What about DBAs using it to elevate their privs as you have just done? It's not the usage of xp_CmdShell that caused that risk. It's the fact that you think DBAs have to have "SA" privs to do their job.

"DBA" is an abbreviation for "Database Administrator"... not "System Administrator". If your DBAs have SA privs and you don't actually want them to be a "security risk", then give them the correct privs for the job instead of blaming xp_CmdShell.

With respect to the proper level of privileges to grant to a production DBA, I agree to disagree. A DBA should have the rights necessary to do her or his job. Generally that requires SA level privileges for the production level DBA, not for the world. The question then becomes: do you trust your DBA? In the case of the original poster of this thread, the poor guy cannot even start the SQL Server agent without jumping through hoops. This seems to me to be excessive control for its own sake, not prudent access management.

I've been in environments where connection strings explicitly log an application in as sa. Luckily, where I am now, that is no longer the case. A DBA's job sometime requires that person to stop and start services, etc. Machine admin is not unreasonable.

Thanks, no worries.

John

You'll get no argument from me that DBAs should have "SA" privs... especially since it allows them to do useful things with xp_CmdShell. Nor will you get an argument from me that you should hire someone that you can trust with the proverbial keys to the city.

I just hate to see something like the incredibly useful xp_CmdShell take it on the chin as a "security risk" when it's usually something else that's the real security risk.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

dwilliscp (12/31/2013)Is there anyway to stop and start the agent, if you are not a local/network admin? As a DBA IT will not give me local or network admin permissions.

Does the account under which SQL Server runs have machine admin permissions? If so, then you could try enabling and using xp_cmdshell to run NET START for the Agent service (please disable xp_cmdshell as soon as you do this; it is a huge security hole). However, you must have sa permissions on the SQL server to do so.

However, the above is a lousy solution. There is no reasonable grounds for not allowing DBA to be machine admin on any SQL Server instance. In fact, I'd argue that such rights are vital to the job of the production DBA.

Jeff Moden (12/31/2013)What privs do you have on SQL Server itself? Do you have "SA" privs? If not, you're pretty much stuck. If you do, then it may be that you have to start SSMS a little differently. I keep an icon on my desktop to start SSMS. If hold down the {Shift} key and right click on the icon, you should see a selection of "Run as Administrator". Click on that and see if you now have control over the agent from SSMS.

Thanks Jeff, I do have SQL Server SA rights, and this Does work for me!

I have to agree with Jeff. If you do not have permissions to restart the agent, have your SA do it. If you need those permissions, put in the request and case for it. Finding a way around a security lock down is exactly why I feel DBA's aren't as trusted as they should be. If there's a declared split in responsibilities, don't work around it. If they get tired of you calling every day for menial tasks that you could do with a bit more permissions, they'll fix your permissions. If not, when that server is down at 2 am and you can't fix it, you can always site back to where you asked for permissions and it was denied.

I wouldn't risk my job over stepping when you can fix the greater issue or just have one less thing to worry about.

As A side note, I've had Sys Admin level access on all but 3 servers I've worked on. It definitely makes the job easier... but that's all. Just easier. The work still gets done and everything still gets fixed, it just requires an additional person on the call.

you have to start SSMS a little differently. I keep an icon on my desktop to start SSMS. If hold down the {Shift} key and right click on the icon, you should see a selection of "Run as Administrator". Click on that and see if you now have control over the agent from SSMS.

Thanks for the Nice Tips, so far I don't know this, its my bad I think not to know this these many years...