Making the GO command live up to its fullest potential

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/06/28/making-the-go-command-live-up-to-its-fullest-potential.aspx

Forcing a DEADLOCK_PRIORITY for a Login?

A question was asked on the MSDN Forums regarding the ability to force a DEADLOCK_PRIORITY level for a specific Login in the database engine.  This seemed at first like it would be something that was in the database engine that I had just never had the need for or opportunity to use.  One suggestion was to utilize a LOGON trigger to SET the option.  My first inclination was that this wouldn't work but rather than hitting the Books Online I decided to give it a shot with the following:

CREATE TRIGGER [EscalateDeadlockPriority]
ON ALL SERVER WITH EXECUTE AS CALLER
AFTER LOGON 
AS
BEGIN
    IF RTRIM(LTRIM(SUSER_SNAME())) = 'logintest'
    BEGIN
        --PRINT 'setting deadlock_priority high';
        SET DEADLOCK_PRIORITY HIGH;
    END 
END

Then I created the testing Login:

CREATE LOGIN [logintest] WITH PASSWORD=N'logintest', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

and finally I tested the connection by using SQLCMD from the command prompt:

sqlcmd -U logintest -P logintest -S oshodb05 -Q "SELECT deadlock_priority from sys.dm_exec_sessions where session_id = @@spid "

This resulted in a return result of 0 which means Normal Priority.  So why won't this work?  The answer is actually really simple, something I already knew had to try this out anyway, and documented in the Books Online in SET (Transact-SQL)

If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.

This is scoped only to the Transaction inside the Trigger, so unfortunately this is not going to solve this problem.  I did some searching online and found the following connect item for SQL Server that documents a request for this:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263809&wa=wsignin1.0

Thankfully the response from Microsoft is that this is in consideration from Microsoft for a future release/edition, though it doesn't seem to have made it into the SQL Server 2008 product line as of RC0.

SQL Injection on the Rise

Microsoft released a new advisory today on the rising number of instances of SQL Injection that are happening on the web. 

To view the security advisory, visit the following Microsoft Web site:

http://www.microsoft.com/technet/security/advisory/954462.mspx

If you have web sites that concatenate unchecked user inputs or parameter line arguments into SQL database calls, then it is important that you fix this problem following the guidelines on this advisory.  This is not all to uncommon, nor is it overly surprising to see this more and more.  This is a common question on the forums, and more than a few posts have been dedicated to trying to help people look into these problems after being attacked.

Securing Production Servers from Developers with SSMS and Application Usernames/Passwords

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/06/18/securing-production-servers-from-developers-with-ssms-and-application-usernames-passwords.aspx