| Author |
Topic |
|
franco
Constraint Violating Yak Guru
255 Posts |
 Posted - 2010-02-24 : 09:48:40
|
| Dear all, I have a strange situation on my new SQL Server 2008 R2 November CTP evaluation system.I have created a new user "TEST" wich is a memeber of the "PUBLIC" Server Role.The problem is that this user is able to shutdown and startup SQL Server.Am I missing something?Please advise.Thank you very much and Best regards.Frank |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-24 : 10:28:27
|
| make sure user is not member of sysadmin server role |
 |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2010-02-24 : 10:32:42
|
| Thank you for your reply.Of course is not member of sysadmin server role.Any idea? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-24 : 10:55:16
|
| I suspect that YOU are the logged in user that can stop/start the service and not test user.How are you doing this? By right-clicking the server in the object explorer? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-24 : 11:00:15
|
| One more thing...are you actually stopping the service, or just noticing that the option isn't greyed out? |
|
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2010-02-24 : 13:52:47
|
| Thank you again for your reply.The fact is that I can stop/start SQL Server by right clicking the server and choose stop/start, and it works.I am connected with the "TEST" login which is a normal login with public server role, nothing else.If I try to do a backup or modify some security attributes, I am not able to do it and that's correct because I don't belong to those server roles (backup operator and security admin).The ability to shutdown the SQL Server service is only for sysadmin and serveradmin fixed servers roles.They have this permission implicitly.So that's really strange.Any idea? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-24 : 14:28:29
|
| Does this tell you anything useful?SELECT * FROM fn_my_permissions(NULL, 'SERVER')if logged in as TEST, otherwise this - but the name specification may need XXX/TEST type UNCSELECT * FROM fn_my_permissions('TEST', 'SERVER') |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-24 : 15:56:19
|
| I still say it was stopped by your account. Is your windows/domain account admin on the box? Check the SQL Server logs. What SPID initiated the shutdown?Stopping services is a windows level permission, not a SQL one. What you're really doing is sending a net stop command to the OS.Have a non-admin user try it...bet it won't work for them. |
|
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2010-02-25 : 03:07:19
|
| Russel you were right.If the windows account is not admin, the start/stop do not work.Thank you very much for your support.Best regards. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-25 : 14:06:17
|
You're welcome. Glad you got it sorted  |
|
|
|
|
Server roles problem