May 16, 2008 at 3:30 pm
I created a simple sql server user with access to a little database for permission testing purposes. I just granted access to "mydatabase" by assigning datareader and datawrite rights to it. It belongs to dbo schema and public server role. Now i need to prevent/deny it from restart the server.
Any idea?
May 16, 2008 at 3:44 pm
As far as I know, the sql server user that you describe should not be able to restart the server.
I am assuming that you tested this and found that the user could do a restart? If so, give specific details as to the steps taken during your test.
Regards,
Rubes
May 17, 2008 at 8:48 am
a read/write user has no such priv, to shut down the sql server u have to be like one of the sysadmin role, local adminstrator, domain admin..
..>>..
MobashA
May 19, 2008 at 2:10 pm
thanks for responses. only to clarify, the following is my test by using SQL Server Management Studio:
1. I created a sql server user named "muhammed"
2. mapped it to "mydatabase" existing database
3. assigned it to datareader and datawriter roles membership
4. kept it in the public role membership, since it comes defaulted.
after above steps, I got logged on the server by using the new user through SQL Server Authentication option. I right-clicked the server name and then "restart" option. The dialog box asked if I was sure, Yes i was and poof...the progress has shown up and all related instance services restarted.
Question: How deny "restart" option from Server/instance node?
May 19, 2008 at 2:20 pm
How did you log onto the server? In SSMS, opening a query window opens a separate connection to the server. The main "Object Explorer" or "Registered Servers" are connected with whatever credentials you used there, likely sysadmin ones.
May 20, 2008 at 8:18 am
You are right Steve. Now I created a user with same name at server level and got logged on by using it. I see now that privileges are more effective, but "restart" option was still enabled. However I did a few more tests and explicitly denied "Shutdown" privileges. Bingo! when you click restart a dialog box pops up stating lack of privileges for such operation. I think that my objective was completely fulfilled, datareader and datawriter rights with no power to restart the server. If you guys have any other opinion, please shoot!
thanks for the prompt response.
May 20, 2008 at 10:14 am
I think some clarification is needed here.
I set up a sql login called Test with no permissions (other than the default inherited through public). In SSMS I went to object explorer, connect --> database engine with the Test login. I was able to right click on the server connection and do a restart. NOT what I expected!!!... and I immediately began to panick at the thought of any login being able to restart.
I then opened a query connection in SSMS connected as Test and tried to run the Shutdown command. Access denied. (Thank goodness!)
So, why can I right click on the server connection (connected as Test) and do a restart? After further testing, I think I've found the answer. When doing a right-click restart, it ALWAYS uses your windows credentials, completely ignoring the credentials of the server connection. So, even though my server connection was connected as Test, the right-click restart was being performed with my windows credentials, which has sysadmin rights. To verify this, I removed my windows account as a login to sql server and also removed it from any windows groups, basically removing all access to the server through windows or sql. Then I attempted to right-click restart and I get access is denied.
So, I think in Fabiano's scenario, the new login never had the ability to restart. Shutdown is only accessible to sysadmin accounts, and a right-click restart is performed with the user's windows credentials.
Regards,
Rubes
May 20, 2008 at 10:22 am
Good catch Rubes!
May 20, 2008 at 9:40 pm
Remember that the connection used to connect Object Explorer to a server can be different from a connection used to open a Query window.
If you fire up a connection to Object Explorer using an admin account then hit the New Query button, that will open a Query Window using the admin credentials. If you then change the credentials used in the Query Window (Query - Connection - Change Connection), it only changes the credentials for the Query Window, not for Object Explorer.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply