Alternative to sp_changedbowner which is being deprecated

SQL Server Books online suggest that will is being deprecated starting from 2008 (not even 2008 R2) and will be removed from future releases of SQL Server.
What is the alternative?
Meet much more flexible command - ALTER AUTHORIZATION - which is available from version 2005 onwards.

so instead of


USE MyDatabase
GO
EXEC sp_changedbowner 'sa'
GO

you may do


USE MyDatabase
GO
ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa;
GO

Convert full datetime to start of the day in T-SQL

Nice trick to convert full datetime to just year-month-day portion, resetting hours, minutes, seconds. Works in T-SQL, SQL Server

This statement:
SELECT GETDATE() AS FullDate, DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101') AS StartOfTheDay

Produces this result:
FullDate StartOfTheDay

2012-04-11 13:51:15.570 2012-04-11 00:00:00.000


Note1: you can put any datetime instead of GETDATE()

Note2: DATEADD(day, DATEDIFF(day, '19000101', GETDATE()), '19000101') is the functional equivalent of DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

VS2008 SP1 may break the Team Foundation Build

Installed VS2008 SP1 on a build server where there was Team Build Agent installed. Started to receive the following error when running the build:


C:\Program Files\MSBuild\Microsoft\VisualStudio\TeamBuild\Microsoft.TeamFoundation.Build.targets(300,15): error MSB4131: The "Reason" parameter is not supported by the "GetBuildProperties" task. Verify the parameter exists on the task, and it is a gettable public instance property.

The reason was that the Tasks assembly was not updated - it is located here:
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\Microsoft.TeamFoundation.Build.Tasks.dll along with some other dlls. For SP1, its version should be 9.0.30729.1, modified in 2008, while in my case it was some lower version.

The solution is to install Team Foundation Server 2008 SP1, which is 133 megs and can be downloaded from here: Team Foundation Server 2008 Service Pack 1 (SP1)

SQL Server Reporting Services 2008 Report Viewer versions

Microsoft.ReportViewer.*.dll


Report Viewer file version 10.0.30319.1 - from VS2010 RTM
Report Viewer file version 10.0.40219.1 - from VS2010 SP1

Nice post about SQL Server Row Level Security Using Schemas

SQL Server Row Level Security Using Schemas

and another one, by Bob Lambert, on Dr Dobbs Magazine

Reexporting SQLCLR assembly as SQL script

Script that allows to import the Sql Server SQL CLR assembly into the database from the binary file and than export it as SQL script. Useful when you need to have a sql script for deployment rather then a dependency on the binary file. Uses tempdb as, well, temprorary storage. I use similar script as part of the Continuous Integration build. To allow that, I had to install SQL Server Express on the build server.

Resulting script will be in asmdump.sql. Remember, you will also need to explicitly add ALL procedures and functions to that SQL script, i.e.
CREATE PROCEDURE [spInsertSomething]
    @CustomerID [int],
    @InvoiceName [nvarchar](50),
    @StartDate [nvarchar](50),
    @EndDate [nvarchar](50)
AS
  EXTERNAL NAME [MYNAME].[StoredProcedures].[spInsertSomething]
GO

REM START ======================================
@echo off
cd /D %~p0

SET DBSRV=%COMPUTERNAME%
SET DBNAME=tempdb
SET CREATORLOGIN=-E

SET ASMNAME=MYNAME
SET DLLNAME=SqlServerProject2.dll
SET LOGFILENAME=log.txt
IF EXIST %LOGFILENAME% del %LOGFILENAME%

SQLCMD.exe -S"%DBSRV%" -d"%DBNAME%" %CREATORLOGIN% -Q"IF  EXISTS (SELECT * FROM %DBNAME%.sys.assemblies asms WHERE asms.name = N'%ASMNAME%') DROP ASSEMBLY [%ASMNAME%]" >> %LOGFILENAME%
 
SQLCMD.exe -S"%DBSRV%" -d"%DBNAME%" %CREATORLOGIN% -Q"CREATE ASSEMBLY %ASMNAME% FROM '%CD%\%DLLNAME%' WITH PERMISSION_SET = SAFE" >> %LOGFILENAME%
 
bcp.exe "SELECT 'CREATE ASSEMBLY [%ASMNAME%] AUTHORIZATION [dbo] FROM ' + master.dbo.fn_varbintohexstr(saf.[content]) + ' WITH PERMISSION_SET = SAFE;' FROM %DBNAME%.sys.assemblies asms INNER JOIN %DBNAME%.sys.assembly_files saf ON saf.assembly_id = asms.assembly_id WHERE asms.name = N'%ASMNAME%' and is_user_defined = 1" queryout asmdump.sql -T -F1 -c -S "%DBSRV%" >> %LOGFILENAME% 

REM uncomment to reimport it into DB for verification
REM SQLCMD.exe -S"%DBSRV%" -d"%DBNAME%" %CREATORLOGIN% -i"%CD%\asmdump.sql" >> %LOGFILENAME%
IF %ERRORLEVEL%==0 GOTO finished

:errored
echo FINISHED WITH ERRORS!
goto done

:finished
echo FINISHED OK!
goto done

:done
pause
REM END ======================================

Howto remap the Windows Account to a SQL Server login

/*

Remaps the Windows Account to a database user. Works with SQL Server 2005, 2008, 2008 R2.

Expects the following variables to come from sqlcmd.exe:

$(DbName) - database name, i.e. AdventureWorks

$(ServerLogin) - the login to use, should be Windows Account, either local or domain. DON'T USE localhost! USE computer name or domain name!!

$(DbUserName) - the user within the database

Usage:

SQLCMD -S"MyServer" -E -i"RemapLogin.sql" -v DbName="AdventureWorks" -v ServerLogin="%COMPUTERNAME%\SomeUser" -v DbUserName="some_user_in_db" -b

*/

USE master

GO

IF EXISTS (SELECT sid FROM master..syslogins WHERE [name]=N'$(ServerLogin)')

BEGIN

PRINT 'Dropping the $(ServerLogin) login';

DROP LOGIN [$(ServerLogin)];

END

GO

PRINT 'Creating the $(ServerLogin) login from Windows Account';

GO

CREATE LOGIN [$(ServerLogin)] FROM WINDOWS

GO

-- Grant server role bulkadmin that's required for BCP.

EXEC master..sp_addsrvrolemember [$(ServerLogin)], 'bulkadmin'

GO

USE [$(DbName)]

GO

PRINT 'Mapping the $(DbUserName) user in DB to newly created login';

GO

/*

Need to detect SQL Server version because LOGIN clause was added only in SQL 2005 SP2

*/

IF EXISTS (SELECT ProductVersion FROM

(SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel) Vrsn

WHERE CAST(ProductVersion AS varchar(100)) LIKE '9.00.%' AND CAST(ProductLevel AS varchar(100)) IN ('RTM','SP1'))

BEGIN

PRINT 'SQL Server 2005 pre-SP2 detected';

ALTER USER $(DbUserName) WITH NAME = $(DbUserName);

END

ELSE

BEGIN

PRINT 'SQL Server 2005 SP2 or later or 2008 detected';

EXEC sp_executesql N'ALTER USER $(DbUserName) WITH NAME = $(DbUserName), LOGIN = [$(ServerLogin)]';

END

GO

PRINT 'FINISHED!';

GO