The database owner SID recorded error when Integrated security= true;

Question

I have a C# application which creates a database, tables using T-SQL code executed by SqlCommand class.

Some scripts which are executed by SqlCommand:

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'clr enabled', 1;
RECONFIGURE;   

use FooDatabase;

if exists (select * from sys.objects where name = 'CreateLineString')
    drop aggregate dbo.CreateLineString;

if exists (select * from sys.objects where name = 'GeographyUnion')
    drop aggregate dbo.GeographyUnion;

if exists (select * from sys.objects where name = 'ConvertToPolygon')
    drop function dbo.ConvertToPolygon;

if exists (select * from sys.assemblies where name = 'osm2mssqlSqlExtension')
    drop assembly osm2mssqlSqlExtension;

create assembly osm2mssqlSqlExtension FROM 0x4D5A900 /* some numbers more here ...*/ 
300000 WITH PERMISSION_SET = UNSAFE;

GO

create aggregate dbo.CreateLineString(@lat float,@lon float,@sort int) returns geography
external name osm2mssqlSqlExtension.[OsmImporter.DbExtensions.LineStringBuilder];
GO 
create aggregate dbo.GeographyUnion(@geo geography) returns geography
external name osm2mssqlSqlExtension.[OsmImporter.DbExtensions.GeographyUnion];
GO 
create function dbo.ConvertToPolygon(@geo geography) returns geography
as external name [osm2mssqlSqlExtension].[OsmImporter.DbExtensions.Functions].ConvertToPolygon;
GO

C# code to execute the above sql code:

protected void ExecuteSqlCmd(string sqlCommand)
{
    var sqlCommands = sqlCommand.Split(
        new[]
        {
            "GO"
        }, StringSplitOptions.RemoveEmptyEntries);

    var connString = Connection.ToString();
    using (var con = new SqlConnection() { ConnectionString = Connection.ToString() })
    {
        foreach (var sql in sqlCommands)
        {
             con.Open();   
             using (var cmd = new SqlCommand() { Connection = con })
             {
                 cmd.CommandTimeout = int.MaxValue;
                 cmd.CommandText = sql;
                 try
                 {
                     cmd.ExecuteNonQuery();
                 }
                 catch (Exception ex)
                 {
                        throw;
                 }
             }
             con.Close();
         }   
     }   
}

When I use the following connection string:

"Data Source=SQL100;Initial Catalog=;Integrated Security=True"

Then I see the following error:

The database owner SID recorded in the master database differs from the database owner SID recorded in database 'FooDatabase'. You should correct this situation by resetting the owner of database 'FooDatabase' using the ALTER AUTHORIZATION statement. Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install. Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install. Changed database context to 'FooDatabase'.

If I use the following connection string, there is no error:

"Data Source=SQL100;Initial Catalog=;User ID=foouser;Password=foopassword

What am I doing wrong? How is it possible to solve this problem? Any help would be greatly appreciated!

UPDATE:

I've tried to use the accepted answer from this question, however I see the following error:

The proposed new database owner is already a user or aliased in the database. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'FooDatabase'. You should correct this situation by resetting the owner of database 'FooDatabase' using the ALTER AUTHORIZATION statement. Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install. Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install. Changed database context to 'FooDatabase'.

I tried reproducing your error but I can't. I seem to be missing one of the steps you are doing. I think you should amend your post describing your setup in more detail, including some c# code and when this is fired. Otherwise we all will keep guessing. For example, the answer provided by @Bibin is only adressing the possible scenario when you try to overwrite your c# created database with another backup (incl different used SIDs). — Thailo, Jan 15 '20 at 08:25
USE [FooDatabase] GO -- Option #1 EXEC sp_changedbowner 'sa' GO -- OR-- -- Option #2 ALTER AUTHORIZATION ON DATABASE::[FooDatabase] TO [sa] GO — Bibin, Jan 15 '20 at 10:08
You just execute this in your sql sever query .And connected this connection string "Data Source=SQL100;Initial Catalog=;Integrated Security=True" — Bibin, Jan 15 '20 at 10:10

Bibin · Answer 1 · 2020-01-15T08:51:01.703

2

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.Try This One

DECLARE @Cmd VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::[<<DatabaseName>>] TO 
[<<LoginName>>]' 

SELECT @Cmd = REPLACE(REPLACE(@Cmd 
            , '<<DatabaseName>>', SD.Name)
            , '<<LoginName>>', SL.Name)
FROM master..sysdatabases SD 
JOIN master..syslogins SL ON  SD.SID = SL.SID
WHERE  SD.Name = DB_NAME()

PRINT @Cmd 
EXEC(@Cmd)

OR

USE [DatabaseName]
GO


    -- Option #1
    EXEC sp_changedbowner 'sa'
    GO

    -- OR--

    -- Option #2
    ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [sa]
    GO

edited Jan 15 '20 at 08:51

answered Jan 15 '20 at 08:16

Bibin

492
5
11

thanks for the reply. But the first my action when I see the above error is to execute this code. However, it throws another error. – Learner Jan 15 '20 at 08:37
I've added an error to my updated question. Please, see my updated question. I believe it is enough information. – Learner Jan 15 '20 at 09:17
I've found a solution. Thanks for your efforts. It was helpful, however I needed to add some code to your solution. I upvoted. Thanks a lot, man!:) – Learner Jan 15 '20 at 12:00

score 1 · Accepted Answer · answered Jan 15 '20 at 11:58

I do not know the reason why this error is occurred. However, the solution was found. Thanks to this great article!. It was necessary to recreate user in newly created database.

The whole code looks likes this:

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'clr enabled', 1;
RECONFIGURE;   

use FooDatabase;

DECLARE @user NVARCHAR(max);
SELECT @user = SL.Name
FROM master..sysdatabases SD 
JOIN master..syslogins SL ON  SD.SID = SL.SID
WHERE  SD.Name = DB_NAME()    

IF ((SELECT 1 FROM sys.database_principals WHERE name = @user) = 1)
BEGIN
    EXEC sp_dropuser @user
END 

DECLARE @Command VARCHAR(MAX) = 'ALTER AUTHORIZATION ON DATABASE::[<<DatabaseName>>] TO 
[<<LoginName>>]' 

SELECT @Command = REPLACE(REPLACE(@Command 
            , '<<DatabaseName>>', SD.Name)
            , '<<LoginName>>', SL.Name)
FROM master..sysdatabases SD 
JOIN master..syslogins SL ON  SD.SID = SL.SID
WHERE  SD.Name = 'FooDatabase'
EXEC(@Command)    


create assembly osm2mssqlSqlExtension FROM 0x4D5A900 /* some numbers more here ...*/ 
300000 WITH PERMISSION_SET = UNSAFE;
/* The other code is omitted for the brevity */

The database owner SID recorded error when Integrated security= true;

2 Answers2