opc.three (4/4/2013)
Eric M Russell (4/4/2013)
Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.Thanks for posting Eric. I'll preface this by saying that I am not meaning to picking on you personally so please do not take it that way. I am picking on the generic scenario which I have a suspicion is probably very common and it goes straight to my point. If someone is tasked with doing something in the host OS'es file system then why do they not have direct access to make those changes in the context of their own account? The person not having access with their own account means someone who controls access in the environment either has not thought to grant those permissions, the DBA has not bothered to ask for those permissions to be granted either for fear of being shot down or because they already found an alternate way to do it without asking, or in the worst case they should not have the permissions at all and yet they gain access via xp_cmdshell. In any case there is a broken process somewhere or someone has violated an access rule, or both.
I have to do these types of tasks as well but I'll either access the server file system over the network via UNC, or I'll RDP to the server to make the file system changes locally. If and when I get around to it I am thinking of exploring Remote PowerShell which would effectively give me a PoSh prompt on my own machine that would actually be running commands on the remote server. Don Jones and Jeffrey Hicks compare Remote PoSh to a telnet session, except routed over HTTP/S which is easier to get routed than telnet these days, and with a PowerShell session instead of a DOS session on the remote computer. In any case, I want to, and I want others to as well, be making the changes under my own security context.
My question would be, why would anyone worry about a DBA doing his/her job? 😉 Being a DBA requires you to do things like this. Some companies grant special permissions to use the methods you used. Other companies grant permissions by saying that you're a DBA and do what you need to do through SQL Server. If you wanted to restrict such actions, it's a relatively simple thing to restrict what the SQL Server and SQL Agent logins can actually "see". If you want to limit even further, you can use PBM against the logins to restrict their actions, right?
The other thing about Eric's task is that he didn't need to use xp_CmdShell to do this. He could have done it through a self-deleting job or through the OPENROWSET hack just as easily even if xp_CmdShell was turned off and never turned on. Why? Because he's a DBA with "SA" privs. It's no different than you being granted privs to do the same thing. The fact that either of you changed some file names isn't going to be logged.
And that brings us back full circle to what I've been talking about. Having xp_CmdShell turned off provides no extra security. If you want good security, just turning off xp_CmdShell isn't going to do it for you.
--Jeff Moden
Change is inevitable... Change for the better is not.