Skip to main content
added 23 characters in body
Source Link
user62379
user62379

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

EDIT:

If you don't wish to go SA as a owner, you could create table containing the database owners

CREATE TABLE DatabaseInAvailabilityGroup.dbo.dbownersMaintenance (
id INT IDENTITY(1,1),
dbname NVARCHAR(100) NOT NULL,
username NVARCHAR(100) NOT NULL
);

Filling up the table (make this as a job and schedule it once a hour or something)

DELETE FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

INSERT INTO DatabaseInAvailabilityGroup.dbo.dbownersMaintenance
SELECT db.name, sl.name
FROM master.sys.databases db
INNER JOIN master.sys.syslogins sl ON db.owner_sid = sl.sid;

As soon as the fail-over has happened, this needs to be run on the new active node:

DECLARE @maxRow INT, @currentRow INT, @sql NVARCHAR(MAX), @dbname NVARCHAR(100), @owner NVARCHAR(85);
SELECT @maxRow = MAX(id), @currentRow = MIN(id) FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

WHILE @maxRow >= @currentRow
BEGIN
SELECT @dbname = dbname, @owner = username FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance WHERE id = @currentRow;
SET @sql = 'ALTER AUTHORIZATION ON DATABASE::'+@dbname+' TO ['+@owner+']';
EXECUTE (@sql);
SET @currentRow = @currentRow+1;
END

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

EDIT:

If you don't wish to go SA as a owner, you could create table containing the database owners

CREATE TABLE DatabaseInAvailabilityGroup.dbo.dbownersMaintenance (
id INT IDENTITY(1,1),
dbname NVARCHAR(100) NOT NULL,
username NVARCHAR(100) NOT NULL
);

Filling up the table (make this as a job and schedule it once a hour or something)

DELETE FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

INSERT INTO DatabaseInAvailabilityGroup.dbo.dbownersMaintenance
SELECT db.name, sl.name
FROM master.sys.databases db
INNER JOIN master.sys.syslogins sl ON db.owner_sid = sl.sid;

As soon as the fail-over has happened, this needs to be run:

DECLARE @maxRow INT, @currentRow INT, @sql NVARCHAR(MAX), @dbname NVARCHAR(100), @owner NVARCHAR(85);
SELECT @maxRow = MAX(id), @currentRow = MIN(id) FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

WHILE @maxRow >= @currentRow
BEGIN
SELECT @dbname = dbname, @owner = username FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance WHERE id = @currentRow;
SET @sql = 'ALTER AUTHORIZATION ON DATABASE::'+@dbname+' TO ['+@owner+']';
EXECUTE (@sql);
SET @currentRow = @currentRow+1;
END

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

EDIT:

If you don't wish to go SA as a owner, you could create table containing the database owners

CREATE TABLE DatabaseInAvailabilityGroup.dbo.dbownersMaintenance (
id INT IDENTITY(1,1),
dbname NVARCHAR(100) NOT NULL,
username NVARCHAR(100) NOT NULL
);

Filling up the table (make this as a job and schedule it once a hour or something)

DELETE FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

INSERT INTO DatabaseInAvailabilityGroup.dbo.dbownersMaintenance
SELECT db.name, sl.name
FROM master.sys.databases db
INNER JOIN master.sys.syslogins sl ON db.owner_sid = sl.sid;

As soon as the fail-over has happened, this needs to be run on the new active node:

DECLARE @maxRow INT, @currentRow INT, @sql NVARCHAR(MAX), @dbname NVARCHAR(100), @owner NVARCHAR(85);
SELECT @maxRow = MAX(id), @currentRow = MIN(id) FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

WHILE @maxRow >= @currentRow
BEGIN
SELECT @dbname = dbname, @owner = username FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance WHERE id = @currentRow;
SET @sql = 'ALTER AUTHORIZATION ON DATABASE::'+@dbname+' TO ['+@owner+']';
EXECUTE (@sql);
SET @currentRow = @currentRow+1;
END
Alternative answer without SA, grammar corrections
Source Link
user62379
user62379

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

EDIT:

If you don't wish to go SA as a owner, you could create table containing the database owners

CREATE TABLE DatabaseInAvailabilityGroup.dbo.dbownersMaintenance (
id INT IDENTITY(1,1),
dbname NVARCHAR(100) NOT NULL,
username NVARCHAR(100) NOT NULL
);

Filling up the table (make this as a job and schedule it once a hour or something)

DELETE FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

INSERT INTO DatabaseInAvailabilityGroup.dbo.dbownersMaintenance
SELECT db.name, sl.name
FROM master.sys.databases db
INNER JOIN master.sys.syslogins sl ON db.owner_sid = sl.sid;

As soon as the fail-over has happened, this needs to be run:

DECLARE @maxRow INT, @currentRow INT, @sql NVARCHAR(MAX), @dbname NVARCHAR(100), @owner NVARCHAR(85);
SELECT @maxRow = MAX(id), @currentRow = MIN(id) FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

WHILE @maxRow >= @currentRow
BEGIN
SELECT @dbname = dbname, @owner = username FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance WHERE id = @currentRow;
SET @sql = 'ALTER AUTHORIZATION ON DATABASE::'+@dbname+' TO ['+@owner+']';
EXECUTE (@sql);
SET @currentRow = @currentRow+1;
END

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';

EDIT:

If you don't wish to go SA as a owner, you could create table containing the database owners

CREATE TABLE DatabaseInAvailabilityGroup.dbo.dbownersMaintenance (
id INT IDENTITY(1,1),
dbname NVARCHAR(100) NOT NULL,
username NVARCHAR(100) NOT NULL
);

Filling up the table (make this as a job and schedule it once a hour or something)

DELETE FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

INSERT INTO DatabaseInAvailabilityGroup.dbo.dbownersMaintenance
SELECT db.name, sl.name
FROM master.sys.databases db
INNER JOIN master.sys.syslogins sl ON db.owner_sid = sl.sid;

As soon as the fail-over has happened, this needs to be run:

DECLARE @maxRow INT, @currentRow INT, @sql NVARCHAR(MAX), @dbname NVARCHAR(100), @owner NVARCHAR(85);
SELECT @maxRow = MAX(id), @currentRow = MIN(id) FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance;

WHILE @maxRow >= @currentRow
BEGIN
SELECT @dbname = dbname, @owner = username FROM DatabaseInAvailabilityGroup.dbo.dbownersMaintenance WHERE id = @currentRow;
SET @sql = 'ALTER AUTHORIZATION ON DATABASE::'+@dbname+' TO ['+@owner+']';
EXECUTE (@sql);
SET @currentRow = @currentRow+1;
END
Source Link
user62379
user62379

Yes, you need to make sure that databases have the same owner, schema changes will travel along the availability group. This gets problematic when there are changes in database owner as you need to reapply those changes too to the secondary nodes.

If it is possible, you could change owner to SA on every database, then make this change every night and when fail-over happens. When you capture the fail-over you can run this to make SA owner for every database:

EXEC sp_MSforeachdb 'ALTER AUTHORIZATION ON DATABASE::? TO SA';