December 2, 2008 at 4:48 am
Hi all,
For BI I want read-only permissions on all sqlserver databases on a server. We will get this done. The problem is however that I have right to (accidentelly) shut down the server. Our DBA doesnot know how to disable that. In other words: when I am logged in on the server with databases I should not be able to push the 'Shutdown' button (after clicking the startbutton in Windows), but only the logoff-button. Does anyone know how we can do this???
December 2, 2008 at 4:53 am
Whay are you connecting diretly to the server, rather than using SSMS from your client machine?
December 2, 2008 at 5:05 am
Another point is "how dumb does the dba think you are"?
I mean if you are trusted with maintaining the system, the data and full access to the core of the business engine, then they must assume you can handle NOT SHUTTING it down.
Also it's a good point about SSMS, I rarely if ever need to RDP directly on the server itself, except to change permissions to some local users or stuff like that. It happens rarely to never.
December 2, 2008 at 5:23 am
You can do this by amending the Local Policy.
Obviously only do this if you are comfortable using the Policy Editor
Start - > Run - > gpedit.msc
navigate to User Configuration - Administrative Templates - Start Menu and Taskbar.
In here edit the setting 'Remove and prevent access to the Shut Down command' and set it to 'Enabled'.
This will also stop alt-F4 being used too.
You will still be able to issue the shutdown command via cmd
Kev
December 2, 2008 at 7:40 am
Dont really understand why your putting this popst in a SQL Admin forum?
What is your role, am gonna assume third line or applications support. That being the case, it frankly non of the dba's business. He's reponsible for database engine NOT global policy. Thats generally set out by the IT usage policy.
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 2, 2008 at 7:45 am
And in response to kevriley, I think you likely get in hot water for making changes to Local Security as it would effect all none admin users of that server which may be undesirable.
Any changes such as this really should be made through and Active Directory GP or Global Policy. Most importantly, if this doesnt make sence DONT DO IT!
You could quite easily turn something on or off or lock certain people/pragrams out and find yourself in even deeper.... umm... trouble.
Adam Zacks-------------------------------------------Be Nice, Or Leave
December 2, 2008 at 7:54 am
leonvr (12/2/2008)
For BI I want read-only permissions on all sqlserver databases on a server. We will get this done. The problem is however that I have right to (accidentelly) shut down the server.
Someone who just needs read-only permission on some databases should not have remote access to the server at all. That should be reserved only for the admins.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2008 at 7:57 am
Schadenfreude-Mei (12/2/2008)
And in response to kevriley, I think you likely get in hot water for making changes to Local Security as it would effect all none admin users of that server which may be undesirable.
Exactly - I don't want anyone to 'accidently' shutdown my server, admin or non-admin! 😀
The only person I trust is me. Even my SysAdmins won't touch the database servers without consulting me first - and that suits me fine. After all it's my neck on the line if the DB isn't there.......
Kev
December 2, 2008 at 8:29 am
I use Remote Desktop quite often too, and in the back of my mind I have always feared 2 things
1. Hit Shutdown instead of Logoff (especially in Windows 2008, the Red button = Shutdown vs Windows 2003/2000)
2. Pick "Shut Down" instead of "Restart" if I want a Restart from the dropdown
I am not stupid, but everyone makes mistakes eventually .... 🙂
I try to not Remote Desktop if I can, but R.D. makes sense over VPN or for long-running queries
December 3, 2008 at 4:03 am
This really does need to be controlled by your Windows Admin people using a GPO. There are Windows rights for 'Sutdown a server' and 'Remote shutdown a server' that need to be restricted.
IMHO, a DBA should have both these rights, but the general user community definitely should not have the rights.
Also, it is definitely poor practice for anyone to directly log on to a DB server to do any T-SQL related work. All SQL access should be done from a client machine. You should only log on to a DB server (either at the console or via RDP, etc) during troubleshooting when nothing else can do the job you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 3, 2008 at 4:49 am
Jerry Hung (12/2/2008)
I try to not Remote Desktop if I can, but R.D. makes sense over VPN or for long-running queries
What my colleagues used to do was RD into a desktop machine (typically their machine) and then run the queries from there. No risk of accidentally shutting down the server and no problem if the connection drops.
When I need to VPN to clients' networks, I always ask them for a non-production machine that I can VPN into.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2008 at 6:46 am
I agree that group policy is the way to go if you MUST disable the shutdown button. Local policy will disable it for everyone.
Keep in mind that disabling the shutdown button is part of the user section of group policy (as it should be), so to get it to apply, you will need to create a policy to the OU (Organizational Unit) where the account is located (and have it affect EVERY machine you log into including your laptop / desktop) or to apply the policy to the OU where the server is located, set it to do loopback processing (which processes the User section of policies affecting the computer/server's OU) AND filter it so that it doesn't apply to those users who need to have the shutdown button (OS Admins, DBA, etc)
If you don't want to go to that extreme, Windows 2003 default is to not let you shutdown or restart without giving a reason, which can be a good check to make sure you really want to shutdown / restart the machine.
Keep in mind that either of these items will only affect the shutdown button GUI. If you run an install and it prompts you to restart, it will do so regardless of policies (you can restart after applying MS patches via Automatic Updates / Windows updates)
December 6, 2008 at 6:09 am
Thanks all for your comments. I mailed a link to this post to the DBA's so they should be able to solve this problem now.
Connecting from the client with SSMS should work, but the DBA's dont have time to install SSMS on the Citrix-servers for the rest of the year...
If anyone expects a problem using SSMS with Citrix, I would like to hear it from you.
Thx again...
December 6, 2008 at 4:15 pm
If you simply behead the first two people that make that mistake, I guarantee the others will be much more careful. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply