A coworker who is upgrading a piece of software asked me today about sysadmin role membership for the login used by said software. Of course, I asked the question as to why that was necessary. This is the response from the vendor’s document on the subject (emphasis added in places):

Why is SQL SysAdmin required for Crestron Fusion installs?

Answer ID: 5441   |    Access Level: Everyone   |    Updated 03/02/2015 03:31 PM


Installing Fusion requires SysAdmin privileges on the Microsoft SQL Server with only one exception.

It is important to backup the database nightly. For this reason, the Fusioninstaller creates a backup job.  SysAdmin is required for this. DBA’s can remove this backup job if there is another backup process in place.  We do this because our software must operate at any customer site including those that have no DBAs.

If the database is not backed up on some regular schedule, the transaction log will eventually fill the SQL server disk where it resides. Room number dependent and disk size dependent, this can happen very quickly. It is extremely difficult to recover from this condition if it happens.

The installer enables some trace flags, also requiring SysAdmin.  The trace flags help our application detect deadlocks and prevent lock escalation.

SysAdmin is required by the installer to run XP_CMDShell.  XP_CMDShell is used responsibly in this manner:

Other XP_ stored procedures that are used

1) Xp_fixeddrives is called to determine physical disk space and for determining the number of physical disks if the user decides to select different disks when creating the database

2) The registry is read using the query below because the create database statement requires a physical disk path

DECLARE @returnValue NVARCHAR(512)

DECLARE @returnData NVARCHAR(512)

DECLARE @returnLog NVARCHAR(512)


set @MasterDB = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N’master.mdf’, LOWER(physical_name)) – 1) FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1)

EXEC xp_instance_regread


@key = N’SOFTWARE\Microsoft\MSSQLServer\Setup’,

@value_name = N’SQLDataRoot’,

@value = @returnValue output

PRINT @returnValue

set @returnValue = ‘SOFTWARE\Microsoft’ + substring(@returnValue, charindex(‘\Microsoft’, @returnValue), len(@returnValue)) + ‘Server’

set @returnValue = ltrim(rtrim(@returnValue))

set @returnValue = replace (@returnValue, ‘\’, ‘\\’)

PRINT @returnValue

EXEC xp_instance_regread


@key = @returnValue,

@value_name = N’DefaultData’,

@value = @returnData output

PRINT @returnData

EXEC xp_instance_regread


@key = @returnValue,

@value_name = N’DefaultLog’,

@value = @returnlog output

PRINT @returnLog

select isnull(@returnData, @MasterDB) DBPath, isnull(@returnLog, @MasterDB) LOGPath


For Replication

1) Xp_cmdshell “sc qc {SQL Server Service Name” – this returns information regarding the SQL Server Service. We need to determine the account ID that is running SQL Server so we know who needs permission to the $REPL data share.

2) XP_CmdShell “sc qc {SQL Server Agent Service Name}” – same as 1

3) XP_CmdShell “ver” – returns the OS Version

4) XP_SubDirs returns all of the sub directories in the master path in search of the REPL data direcotry

5) XP_CMDShell is called to run CACLS or ICACLS – OS Dependent to guarantee that the SQL Agent Login Account and SQL Server Login account have sufficient permission on the REPL data directory.


Not For Replication

1) XP_CmdShell configures the SQL Agent to start automatically and starts it if it is stopped. This is necessary to run our SQL Agent jobs. Even in a single database model the nightly energy job is necessary.


Please note: Replication has been deprecated and is no longer supported in Fusion 10.x.

Cool! You just need to create some random backup jobs, turn on some trace flags, and enable xp_cmdshell? I guess I’m the jerk for asking the question…

DATETIME2 Partition Functions and SSMS Scripting Weirdness

Kendra Little’s blog post on DATETIME2 and Partition Elimination today reminded me of an interesting behavior in SQL Server Management Studio that I encountered recently. After putting together a partitioned database on my workstation, scripting it out, and deploying it to a test server, I started seeing problems with partition switching because partitions that should have been empty were ending up with data in them.

After a bit of investigation, I discovered that it seems that regardless of the precision specified when you create a partition function with an input parameter type of DATETIME2, if you later use SSMS to generate a CREATE script of that partition function, it will default to a precision of (3).

Take the following script, for example:

AS RANGE LEFT FOR VALUES (N'2016-01-31T23:59:59.9999999', N'2016-02-29T23:59:59.9999999', N'2016-03-31T23:59:59.9999999');

After creating this partition function, I can then generate the CREATE script by right-clicking on the partition function in Object Explorer and be presented with this:

CREATE PARTITION FUNCTION [pf_MonthsLeft](DATETIME2(7)) AS RANGE LEFT FOR VALUES (N'2016-01-31T23:59:59.999', N'2016-02-29T23:59:59.999', N'2016-03-31T23:59:59.999')

The data type is still DATETIME2(7), but the range values have all been truncated to 3 digit precision. In a RANGE RIGHT scenario where it’s all zeros after the decimal, you might not even notice. However, in the example above, you could end up with a partitioned table where a row with a time stamp of ‘2016-01-31T23:59:59.9000001’ ends up in one partition, while ‘2016-01-31T23:59:59.9990001’ ends up in the next partition.

I can’t think of a real-world situation where I would have partitions smaller than milliseconds, but consider this partition function:

AS RANGE LEFT FOR VALUES (N'2016-01-31T23:59:59.9990000', N'2016-01-31T23:59:59.9999999')

SSMS generates the script for this one with two identical values:

AS RANGE LEFT FOR VALUES (N'2016-01-31T23:59:59.999', N'2016-01-31T23:59:59.999')

If you tried to run that script, you would be presented with this nice error:

Msg 7708, Level 16, State 1, Line 5
Duplicate range boundary values are not allowed in partition function boundary values list. Partition boundary values at ordinal 1 and 2 are equal.

Once again, this proves there is really no substitute for writing (and keeping) your own scripts. I won’t make that mistake again.