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)

Microsoft drops support for OLE DB after SQL Server Denali is released

“

ODBC is the de-facto industry standard for native relational data access, which is supported on all platforms including SQL Azure. Cloud is universal and in order to support all client applications connecting from any platform to the cloud, Microsoft has been fully aligned with ODBC on SQL Azure, as ODBC is the only set of APIs that are available on all platforms including non-Windows platforms.

The next release of Microsoft SQL Server, codename “Denali”, will be the last release to support OLE DB. OLE DB will be supported for 7 years from launch, the life of Denali support, to allow you a large window of opportunity for changing your applications before the deprecation. This deprecation applies to the Microsoft SQL Server OLE DB provider only. Other OLE DB providers as well as the OLE DB standard will continue to be supported until explicitly announced.

We encourage you to adopt ODBC in the development of your new and future versions of your application.

”

http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx

http://www.infoq.com/news/2011/09/OLE-DB-End

I remember that they wanted OLE DB and ADO.Net be their “universal data access”…. Obviously, they are changing direction now. Well, we have 7 years to adapt

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