Sergiy (10/13/2013)
L' Eomot Inversé (8/10/2013)
but in SQL Server 2000 if people without SA access had access to xp_cmdshellHow?
I can't speak for Tom but apparently through methods similar to what they are today but with the possible fault that Tom speaks of. Here's a copy of what BOL 2000 sp3 has to say on the subject. I can't confirm the behaviour that Tom was speaking of because I no longer have SQL Server 2000 loaded anywhere.
By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
Note In earlier versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account. In SQL Server 7.0, the account is called SQLAgentCmdExec. Users who are not members of the sysadmin fixed server role now run commands in the context of this account without specifying a configuration change.
Permissions
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
To summarize my opinion about all of that, it's still bloody foolish to grant non-SA prived users the privs to run xp_CmdShell directly. It's ok to give them privs to execute a stored procedure (that they can't change) that uses it or, maybe, through a job that they can start but not change but they should never be given privs to use it directly (that also means that no application login has SA privs). Otherwise, xp_CmdShell is safe to use by trusted SA's, in stored procedures, and in jobs.
--Jeff Moden
Change is inevitable... Change for the better is not.