The database owner SID recorded in the master database differs from the database owner SID recorded in database

The database owner SID recorded in the master database differs from the database owner SID recorded in database '<Database>'. You should correct this situation by resetting the owner of database '<Database>' using the ALTER AUTHORIZATION statement.

This problem can arise when a database restored from a backup and the SID of the database owner does not match the owners SID listed in the master database.

To correct this issue, run the following command:

Alter Authorization on Database::<Database> to [<USER>]

To check who is the owner listed in the master database run the following:

SELECT  SD.[SID]

       ,SL.Name as [LoginName]

  FROM  master..sysdatabases SD inner join master..syslogins SL

    on  SD.SID = SL.SID

 Where  SD.Name = '<Database>'

To check what SID is the DBO in the restored database run the following:

Select [SID]

  From <Database>.sys.database_principals

 Where Name = 'DBO'

Why won’t my log file shrink?

While it is best practice to never shrink a database or individual file in SQL Server, there are times that it has to be done. Whether you have a test database that sits on an undersized test server or a production database that had a once in a blue moon transaction go though that bloated the log file, you may find yourself needing to recover the space.

If you need to shrink the log file, first you need to find the logical file name. You can do this by connecting to the bloated database and running the following:

Select Name, Type_Desc

  From sys.database_files

 Where Type_Desc = 'Log'

Then take that name and run the following DBCC command:

dbcc shrinkfile('<logical_logfile_name>',10)

The 10 signifies leaving 10% free space. If you want to recover the maximum space, use TRUNCATEONLY in place of 10.

But sometimes when you use this command nothing happens, why? In the messages when I ran the DBCC command, I see the following:

Cannot shrink log file 2 (<logical_logfile_name>) because all logical log files are in use.

Hmm, there’s plenty of space in the log I should be able to recover but cant. Why? Let’s look at the log_reuse_wait_desc column in sys.databases:

SELECT  Name,Recovery_Model_Desc, Log_Reuse_Wait_Desc

FROM    Sys.databases

Where   Name = <'db_name'>

On FULL or BULK_LOGGED databases you may see LOG_BACKUP, while on SIMPLE recovery model databases you may see CHECKPOINT. If the recovery model is simple, issue a checkpoint (which writes all the dirty pages in the buffer cache to disk) and then try the shrink again.

If the recovery model is FULL or BULK_LOGGED, we need to issue a backup log command to free up part of the active log: You can do that by issuing the following command:

backup log <db_name> to disk = '<file_name>'

Books Online states that it may take 2 log backups to actually free the space.

After this, you should be able to use DBCC SHRINKFILE to shrink the log file.

NOTE: IF YOU ARE USING THE FULL OR BULK_LOGGED RECOVERY MODEL YOU SHOULD BE TAKING LOG BACKUPS ON A SCHEDULED BASIS. NOT ONLY WILL THIS KEEP YOUR LOG FROM GROWING UNCONTROLLABLY, BUT IT WILL ALSO SAVE YOUR BACON IF YOU EVER HAVE TO RESTORE THE DATABASE FROM A BACKUP, WHICH IS THE MOST IMPORTANT REASON.

Find all the Stored Procedures where a user has been explicitly granted or denied access (and script them)

This will list all the stored procedures where a user has been explicitly granted or denied certain access

Select State_Desc,

       Permission_Name,

       Object_Schema_Name(Major_ID) as [SP_Schema],

       Object_Name(Major_ID) as [SP_Name],

       User_Name(Grantee_Principal_ID) as [Principal_Name], *

  From sys.database_permissions PERM inner join sys.database_principals PRIN

    on PERM.Grantee_Principal_ID = PRIN.Principal_ID

 Where PERM.Class = 1 

   and PRIN.Name = '<UserName>' 

 Order by Object_Name(Major_ID)

This will generate a script that will recreate that access

Select State_Desc + ' ' + Permission_Name + ' On ['

       + Object_Schema_Name(Major_ID) +

       '].[' + Object_Name(Major_ID) + '] to ['

       + User_Name(Grantee_Principal_ID) + ']'

  From sys.database_permissions PERM inner join sys.database_principals PRIN

    on PERM.Grantee_Principal_ID = PRIN.Principal_ID

 Where PERM.Class = 1

   and PRIN.Name = '<UserName>' 

 Order by Object_Name(Major_ID)

How to remove the msrepl_tran_version column from a table

       When transactional replication with immediately updating subscriptions is created, a column called msrepl_tran_version is added to any tables (articles) in the publication along with a default constraint to populate it. Now if you ever remove an article from that publication or remove the publication completely, the column remains. To remove the column, first you have to remove the constraint and then the columns. The script below creates the commands to remove both.

/*Create Script to drop constraint and remove columns*/

Select 'IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''' + DC.Name + ''') and Type = ''D'')

            ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.Name + '] DROP CONSTRAINT [' + DC.Name + ']'         

From SysObjects SO inner join SysColumns SC

    on SO.ID = SC.ID

                      inner join sys.default_constraints DC

    on SO.ID = DC.Parent_object_id

   and SC.colid = DC.Parent_column_id

 Where SO.XTYPE = 'U' 

   and SC.Name = 'msrepl_tran_version' 

union

Select 'IF EXISTS (SELECT * FROM dbo.syscolumns where id = OBJECT_ID(N''' + SO.Name + ''') and Name = ''msrepl_tran_version'')

           ALTER TABLE [' + OBJECT_SCHEMA_NAME(SO.ID) + '].[' + SO.Name  + '] DROP COLUMN [msrepl_tran_version]  '        

From SysObjects SO inner join SysColumns SC

    on SO.ID = SC.ID

                      inner join sys.default_constraints DC

    on SO.ID = DC.Parent_object_id

   and SC.colid = DC.Parent_column_id

 Where SO.XTYPE = 'U' 

   and SC.Name = 'msrepl_tran_version'

 order by 1 

Adding a table (article) to transactional replication without regenerating (recreating) the entire snapshot.

I’m using transactional replication to move data off of a production server and on to a reporting server to keep the load off production when writing real time reports. From time to time, I will get a request to add a table to the replication set so that it can be utilized in various reports. This has typically been done after work hours because adding an article meant recreating the snapshot, which sometimes took more than an hour. Then I started thinking there has to be a better way to add a table without the entire snapshot being recreated, and it turns out there is, I just had the replication setup incorrectly.

When setting up transactional replication thru the SSMS GUI, there are many options hidden from you or are worded in a way that is counter-intuitive (at least to me anyway). One of those options is “Create a snapshot immediately and keep the snapshot available to initialize subscriptions”.

At first glance, I thought “Sure I want to create the snapshot immediately after I create the publication since I want to transfer it to the new subscriber as soon as possible”. In the background this sets the @immediate_sync in the exec sp_addpublication to TRUE so that ALL the synchronization files are created or recreated EACH time the snapshot agent is ran. After the publication has been created you can confirm this by running:

   exec sp_helppublication ‘<publication_name>’

Look at the immediate_sync column, you should see a 1.

Now, how do we fix it to allow for the addition of a new table (article) without having to generate the entire snapshot? We have to change 2 things, immediate_sync and allow_anonymous on the publication.

NOTE: You must also have an independent, stand-alone distribution agent for the publication.

exec sp_changepublication

@publication = '<publication_name>',

@property = 'allow_anonymous', @value = 'False'

exec sp_changepublication

@publication = '<publication_name>',

@property = 'immediate_sync', @value = 'False'

After these options have been set on the publisher, you will be able to add an article via the GUI or script and transfer it over to the subscriber without generating the entire snapshot. From now on, after a table (article) is added and the snapshot agent is ran, the snapshot will only contain the new table that has been added. If you choose to REINITIALIZE your subscription before you create the snapshot or have new subscriptions waiting to be initialized, the ENTIRE snapshot with all the articles will still be created.

Disabling Triggers on Replicated Tables (Execute As)

I was recently given the requirement to allow for a user process to disable the triggers on a table. This can be done easily by granting the user Alter rights on the table in SQL 2005/2008, but this table is replicated and only owners or sysadmins can disable the triggers on replicated tables.

Below is the error message the user received when trying to disable the triggers on the replicated table:

Msg 21050, Level 14, State 1, Procedure sp_MSreplcheck_publish, Line 16

Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

The “solution”, and I put it in quotes because I’m not real fond of it, is to grant this user the right to impersonate an elevated account, in this case, DBO.

NOTE: You should be very careful when granting impersonation and understand the security consequences when granting those rights. I would always abstract any code that is doing impersonation with a Stored Procedure.

First, this must be ran on the database where the user is needing to disable the triggers

Grant IMPERSONATE ON User:: dbo to [<user>]

Then, inside a Stored Procedure, the following code would be executed:

Execute AS USER =  'dbo'

Alter Table <Table> Disable Trigger <triggername>|| ALL

Revert

……..Code

Execute AS USER =  'dbo'

Alter Table <Table> Disable Trigger <triggername>|| ALL

Revert

The REVERT keyword is very important in this case because after the trigger has been dropped, we want the user to REVERT back to using their own security.

ANSI_Padding and Scripting Tables from SSMS

The other day I wanted to quickly create a table from an existing table in another database. If you are using SSMS (SQL Server Management Studio) 2005/2008, you know the quickest way to do this is to right click on the table and “Script Table As” à “Create To” à “New Query Editor Window”. When this is done, you would expect a clean create table script, with defaults, primary and foreign keys, but instead I received the following:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Table1](

       [col1] [int] NULL,

       [col2] [varchar](20) NULL,

       [col3] [money] NULL,

       [col4] [timestamp] NOT NULL,

       [col5] [uniqueidentifier] NULL,

       [col6] [xml] NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[Table1] ADD [col7] [varchar](30) NULL

ALTER TABLE [dbo].[Table1] ADD [col8] [char](25) NULL

ALTER TABLE [dbo].[Table1] ADD [col9] [nchar](40) NULL

ALTER TABLE [dbo].[Table1] ADD [col10] [nvarchar](10) NULL

SET ANSI_PADDING ON

ALTER TABLE [dbo].[Table1] ADD [col11] [varchar](30) NULL

ALTER TABLE [dbo].[Table1] ADD [col12] [char](25) NULL

ALTER TABLE [dbo].[Table1] ADD [col13] [nchar](40) NULL

ALTER TABLE [dbo].[Table1] ADD [col14] [nvarchar](10) NULL

GO

SET ANSI_PADDING OFF

GO

Why aren’t all the columns created in the initial “Create Table” statement? The answer lies in how the columns were initially added to the originating table.

When using SSMS to create objects, certain ANSI settings are set on the connection to the server. To view these ANSI settings, navigate to “Tools” à “Options” à “Query Execution” à “SQL Server” à “ANSI”

So, by default, “Set ANSI_PADDING” is set to ON for all connections to the server opened via SSMS.

One problem is that not all tools are nice enough to set these options for you automatically. If, for example, I used SSMS to create this table initially:

How To Determine What Applications Are Accessing SQL Server

When upgrading or decommissioning an existing server, one of the things you will need to know is what applications are accessing data on the server? The first place to start is SQL Server Profiler (Trace). By choosing the correct events to capture, this tool will allow you to track all the applications and users that login to the server and access SQL for as long as the trace is running.

Since you want your trace to be as light-weight as possible (server side tracing is also an option here), you will only select one event, Audit Login. By default the TextData, ApplicationName, NTUserName, LoginName, ClientProcessID, SPID, and StartTime are selected. Another piece of handy information is knowing where the request originated from, i.e. HostName. To see the HostName column, check Show All Columns.

Once you’ve done this click Run. Now, every time an application connects to SQL Server, you will see the name of the application, the user that is connecting, and what computer originated the request. Using this information as a starting point, you should be able to begin preparations for testing applications in the event of an upgrade or moving connections in the case of decommissioning.

Visual Studio 2010 Database Projects Warning SQL04151: Unresolved Reference

In Visual Studio 2010 Database Projects you may get a SQL04151 Unresolved Reference warning when building a solution that contains multiple database projects. This can stem from a multitude of things including unresolved or ambiguous references in store procedures, references to linked servers, or incorrectly setup database references in the project.

You have two options:

1.       Fix all the references. When all you have to do is fix scripts that have table aliases but the column aliases are missing, it’s pretty easy. But when Visual Studio starts barking about linked server references, it’s a bit harder to get rid of those warnings. Example below

Instead of:

Select Col1

      ,Col2

  From Table1 T1 inner join Table2 T2

    on T1.Col1 = T2.Col1

Use:

Select T1.Col1

      ,T2.Col2

  From Table1 T1 inner join Table2 T2

    on T1.Col1 = T2.Col1

Or in the instance of cross-database queries, you may have to replace the hard coded database name with a variable that is defined in the .sqlcmdvars file of the project

For instance,

                Select * From DatabaseName.dbo.Table

Would become

                Select * From [$(DBName)].dbo.Table

2.       Suppress SQL04151 warnings in your project. You can do this by right clicking on your database project and selecting properties. Once you are there, you should see a “Build” tab on your left side.  In the “Suppress Warnings” check box, input 4151. Note: If you are using multiple configurations, you may want to select “All Configurations” at the top of the page.