SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Non-admin Users SQL Server Configuration Manager
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sarobanares
Starting Member

5 Posts

Posted - 07/14/2014 :  03:35:43  Show Profile  Reply with Quote
Hi Everyone,

I'm wondering if it's possible to grant non-admin users to start/stop SQL services using SQL Server Configuration Manager. Using the sc command, I was able to grant non-admin users access to services applet in Windows. However, our DBA said that the best practice to stop/start SQL is via SQL Server Configuration Manager. Based on testing, they are unable to stop/start SQL from SQL Server Configuration Manager though they are able to do so (stop/start SQL services) via Services applet.

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/14/2014 :  12:14:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
I disagree with your DBA.

If it's a standalone instance, then using the services applet is fine. If the SQL instance is clustered, then you can use cluster manager. SQL Server Configuration Manager can be used in both cases, but it certainly does not need to be the only tool.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sarobanares
Starting Member

5 Posts

Posted - 07/14/2014 :  19:15:06  Show Profile  Reply with Quote
Hi tkizer, regardless of what the DBA says. The question still lies if it's possible for a non-admin user to stop/start SQL services from SQL Server Configuration Manager.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/14/2014 :  19:22:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Please post the error message.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sarobanares
Starting Member

5 Posts

Posted - 07/14/2014 :  21:55:33  Show Profile  Reply with Quote
Hi tkizer, actually there's no error message generated (as far as I'm concerned). Our DBA can open SQL Server Configuration Manager. However, when they try to stop the service a progress bar appears showing that the service is stopping. Once the progress bar finishes, the service still shows that it is running as if a stop request was not submitted at all.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/14/2014 :  23:32:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
When the DBA does it, is it on the same machine as the other lesser privileged people? In other words, the DBA is actually able to stop/start using Config Manager on the same machine that "they" can't?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/14/2014 :  23:33:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
If the answer is yes, I would check the Application Log in Event Viewer as there should be something in there for attempting to stop the service and that might lead you to the right place. I suspect local admin/sysadmin are required for this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sarobanares
Starting Member

5 Posts

Posted - 07/16/2014 :  19:44:10  Show Profile  Reply with Quote
Hi tkizer,

Our DBA is a member of the DB server's Remote Desktop local group. They connect to the DB server using remote desktop. Using the Services applet on the DB server, they can stop/start SQL services. However, if they were to use SQL Server Configuration Manager on the DB server, when they click on stop button a progress bar appears and when it finishes SQL service is still running and no message dialog box appears and no logs are generated on the server's event logs.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/16/2014 :  19:49:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
Try running it in Administrator mode (right click, Run as Administrator).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sarobanares
Starting Member

5 Posts

Posted - 07/16/2014 :  22:46:57  Show Profile  Reply with Quote
Hi tkizer,

Doing that will ask for an administrator password. Remember, the DBAs are ordinary users. The objective is to stop/start SQL services as a non-admin user using SQL Server Configuration Manager.
Go to Top of Page

Shanky
Starting Member

United Kingdom
48 Posts

Posted - 07/17/2014 :  06:52:34  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

I disagree with your DBA.

If it's a standalone instance, then using the services applet is fine. If the SQL instance is clustered, then you can use cluster manager. SQL Server Configuration Manager can be used in both cases, but it certainly does not need to be the only tool.


Tara I humbly disagree. SQL Server configuration manager(SCCM) should be the only place to start stop SQL server services in standalone SQL server instance even in cluster SSCM should be preferred over cluster GUI. SCCM preserves the ACL's. So actually DBA is correct

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

Edited by - Shanky on 07/17/2014 06:53:04
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 07/17/2014 :  13:04:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by Shanky

quote:
Originally posted by tkizer

I disagree with your DBA.

If it's a standalone instance, then using the services applet is fine. If the SQL instance is clustered, then you can use cluster manager. SQL Server Configuration Manager can be used in both cases, but it certainly does not need to be the only tool.


Tara I humbly disagree. SQL Server configuration manager(SCCM) should be the only place to start stop SQL server services in standalone SQL server instance even in cluster SSCM should be preferred over cluster GUI. SCCM preserves the ACL's. So actually DBA is correct




I realize that's the stance of Microsoft, but I still disagree. I do agree it should be used to make changes to the service accounts, ports, etc. Over the many years I've been working on SQL Server, I've never once had an issue with stopping/starting services in the tools I mentioned. I do use SSMS for failovers of AGs though.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000