Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Non-admin Users SQL Server Configuration Manager

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-14 : 19:22:40
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 - 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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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 correct

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

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -