| Author |
Topic |
|
sarobanares
Starting Member
5 Posts |
 Posted - 2014-07-14 : 03:35:43
|
| 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
38200 Posts |
Posted - 2014-07-14 : 12:14:24
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
sarobanares
Starting Member
5 Posts |
Posted - 2014-07-14 : 19:15:06
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-14 : 19:22:40
|
| Please post the error message.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sarobanares
Starting Member
5 Posts |
Posted - 2014-07-14 : 21:55:33
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-14 : 23:32:12
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-14 : 23:33:02
|
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sarobanares
Starting Member
5 Posts |
Posted - 2014-07-16 : 19:44:10
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-16 : 19:49:33
|
| Try running it in Administrator mode (right click, Run as Administrator).Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sarobanares
Starting Member
5 Posts |
Posted - 2014-07-16 : 22:46:57
|
| 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. |
|
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-07-17 : 06:52:34
|
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 correctRegardsShankyhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-17 : 13:04:31
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Non-admin Users SQL Server Configuration Manager