Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««12345

What is your favorite "I didn't know that" moment in T-SQL? Expand / Collapse
Posted Sunday, October 13, 2013 11:42 PM



Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 5,669, Visits: 11,173
What is your favorite "I didn't know that"...

Where to start?

Drag and drop columns from Object Explorer in Query Analyzer?

Declaring column names in derived tables in a way later used in CTE?
select 1, 2
) DT (ID, Value)

Running totals trick with UPDATE using accumulating variables?

I'm sure I'll remember more when I'm offline...
Post #1504321
Posted Monday, October 14, 2013 2:06 AM



Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 2,015, Visits: 22,617
Amongst others and in no particular order

Numbers/tally table
Calendar tables
Gap and islands via ROW_NUMBER


Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

Post #1504343
Posted Monday, October 14, 2013 5:22 AM



Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 42,072, Visits: 39,455
Sergiy (10/13/2013)
L' Eomot Inversé (8/10/2013)
but in SQL Server 2000 if people without SA access had access to xp_cmdshell


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.

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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1504405
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse