Today I was checking an older server and decided to run sp_helpdb. On a bunch of databases I noticed that the owner was ~~UNKNOWN~~. The only reason I noticed this was when I tried to look at a specific database which is mirrored. I was greeted with thi…

I had to change the account that some SQL Server boxes were using to run the SQL Server services. After I changed the account on one machine from DomainName\User to DomainName\NewUser SQL Server wouldn't start up In the error log I saw the following…

If you look at the sys.sysobjects view, you will see an xtype column listed Object type. Can be one of the following object types: AF = Aggregate function (CLR) C = CHECK constraint D = Default or DEFAULT constraint F = FOREIGN KEY constraint L…

If you need to use a Dedicated Administrator Connection (DAC) via SSMS, you can't just use the Connect Object Explorer, if you try you will get the following error Dedicated administrator connections are not supported via SSMS as it establishes multi…

April Fools' Day is a day when people play practical jokes and hoaxes on each other. Why not trying to play some practical jokes on your friendly DBA :-) The first thing we are going to do is to spoof the host and program name. This is easy to do. Click on Connect, choose Database Engine, you will see the following box

Yesterday I blogged about using WITH RESULT SETS with the EXECUTE command here: Use WITH RESULT SETS to change column names and datatypes of a resultset. Naomi Nosonovsky left the following comment Can you use this feature to get result sets from the s…

SQL Server 2012 has added WITH RESULT SETS to the EXCECUTE command. You can now override the data types and names that the resultset is returning EXEC ('SELECT name FROM sys.tables' ) WITH RESULT SETS ( (TableName nvarchar(100)) ); Table…

Sometimes you have to insert a bunch of data and you can't use BCP or another bulk load method. When you do single row inserts, SQL Server wraps these inserts inside an implicit transaction. Did you know that if you use an explicit transaction that the…

If you are into Business Intelligence, you are in luck, this week Microsoft has released a SQL Server 2012 Business Intelligence whitepaper as well as SQL Server Data Tools – Business Intelligence for Visual Studio 2012 (SSDT BI) templates. The white…

There was a requirement to create a new user who would have only insert permissions to one table, this user would also have insert and select permissions to another table. This is pretty simple to accomplish. First create this simple database with tw…