Friday, June 29, 2007

Recognising the SQL Server Version

Something I often need to do is recognise which SQL Server version I am working on. For example to separate SQL 2000 compatible code from SQL Server 2005 compatible code. I'll show you some of the options to get the version number... Starting from the ugliest to the most elegant... The first option is calling the extended procedure xp_msver

exec xp_msver 'ProductVersion'
Which give you this result:
xp_msver can give you quite a large amount of version information about your system, but it is not very pretty to use in your code. There is also no option to get a simple version number. Another option is to call @@version
select @@version
Which gives you something that looks like:
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
This is of course not very practical when you are programming. So another option is fetching the version number from the server properties.
select serverproperty('productversion')
And the result looks a bit like this:
This is already more practical. But still difficult to use in your programs. My favorite option is using the @@microsoftversion function. This function returns a
select @@microsoftversion / 0x01000000 
And the result of this query is a very simple:
The following query shows you the possible values for the recent SQL Server versions (I don't have a SQL 6 - 7 test environment anymore, so I don't know if it works on those.)
SELECT CASE @@microsoftversion/ 0x01000000 
         WHEN 8 THEN 'sql 2000'
         WHEN 9 THEN 'sql 2005'
         WHEN 10 THEN 'sql 2008'
Although this was a simple topic, I hope you found it interesting.

Tuesday, June 26, 2007

Using SET IDENTITY_INSERT as a normal database user in SQL Server 2005.

In earlier versions of Microsoft SQL Server it was impossible to grant "SET IDENTITY_INSERT" rights to a normal user. To have permission to do an identity_insert (even as part of a stored procedure) you needed to grant the user ddl admin rights which is quite a heavy permission to grant to a normal user.

Luckily this problem can be solved in SQL Server 2005 by using Certificates and code signing.

Let's set up a demo environment.

create database identity_insert_test
use identity_insert_test
exec sp_addlogin 'SimpleLogin', 'PasswordForSimpleLogin'
exec sp_grantdbaccess 'SimpleLogin', 'SimpleLogin'

if exists(select * from sys.objects where object_id = object_id('dbo.a')) 
   drop table dbo.a
create table dbo.a (keyfield int identity, somevalue varchar(10))

if exists(select * from sys.objects where object_id = object_id('p_a')) 
  drop procedure p_a
create procedure p_a
set identity_insert dbo.a on
insert into dbo.a (keyfield, somevalue) values (2,'nice')
set identity_insert dbo.a off

return (0)

grant exec on p_a to SimpleLogin

When we run the stored procedure with the simple "no rights" login we quickly see the following error

Msg 1088, Level 16, State 11, Procedure p_a, Line 3
Cannot find the object "dbo.a" because it does not exist or you do not have permissions.
(Make sure you run the "revert;" part of the statement to revert to your login after the error.)

Execute as user = 'SimpleLogin'
exec p_a

To fix this annoying problem we need to create a few more objects in the database. First of all we need a master key which will be used to encrypt the certificate. Then we create a certificate, a login based on the certificate and we grant the "ALTER ANY SCHEMA" permission to the login. And finally we will sign the stored procedure with the schema login.

CREATE MASTER KEY ENCRYPTION BY password = '**somethingspecial**';
CREATE CERTIFICATE SchemaCert WITH SUBJECT = 'Schema user Certificate'

Now when we rerun the stored procedure as our simple login we see that everything works as expected.

Execute as user = 'SimpleLogin'
exec p_a

The ability to sign stored procedures gives you the power to build a much more secure SQL Server Database system. For example you can sign stored procedures that use dynamic sql and remove the need to grant select rights on the underlying tables.

I have not (yet) benchmarked the performance impact of signing stored procedures, but I suspect the overhead will not be huge.

You can download the complete test script and try it out.

Tuesday, June 5, 2007

a Daylight savings time aware version of a sqlclr xp_getfiledetails procedure.

One of the undocumented extended stored procedures that no longer exists in SQL Server 2005 is xp_getfiledetails.

Of course I am aware that I am not the first one to implement this procedure as a SQLCLR procedure.

I found implementations on the following locations:

But a bug I noticed in both the versions of the procedure is the fact that it is not "Daylight Savings Time" aware.

This means that if you request the file details of a file that is created on a day when Winter Time was in place and currently Summer Time is in use that the timestamps are off 1 hour.

To correct this problem this I wrapped the datetime checks in the following function:

private static TimeSpan ExplorerBias(DateTime timeStamp)
    TimeSpan bias = new TimeSpan(0);
    if (TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now) != TimeZone.CurrentTimeZone.IsDaylightSavingTime(timeStamp))
        bias = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now) - TimeZone.CurrentTimeZone.GetUtcOffset(timeStamp);
    return bias;

You can download the Complete xp_getfiledetails sqlclr Script and see the effect of the function.

To test the script, create a new Visual Studio 2005 Database project and paste the code from the script (or just include the file.)