UNDERCOVER TOOLBOX: sp_ChangeDatabaseOwnership – Producing statements to Alter Authorisation

 

The latest addition to our toolbox is not a one size fits all, this procedure may or may not suit your needs but if you have a requirement of setting database ownership’s to a specific login across the board such as SA, then this Stored procedure may well be for you.

The idea of the procedure is to check all database ownership’s for a specific Login, lets assume SA – when this checks the databases if it finds a database where the SID does not match the login passed in then it will show the results and include two statements:

• An ALTER Authorisation statement for the database and Database Owner you have specified
• An ALTER Authorisation statement for the database and Current Database Owner so that you can revert if necessary

Here is an example:

sp_changeDatabaseOwnership 'SA'

Results:

Column: ChangeToNewOwner

ALTER AUTHORIZATION ON DATABASE::[SQLUNDERCOVERDB] TO [SA];
ALTER AUTHORIZATION ON DATABASE::[SQLUNDERCOVERTRAINING] TO [SA];

Column: RevertToOriginalOwner

ALTER AUTHORIZATION ON DATABASE::[SQLUNDERCOVERDB] TO SQLUndercoverUser];
ALTER AUTHORIZATION ON DATABASE::[SQLUNDERCOVERTRAINING] TO [SQLUndercoverUser];

If we pass in a Login that does not exist an error will appear as below:

Here is the code:

 USE [master]
go

/**********************************************
--Procedure Name: sp_ChangeDatabaseOwnerShip
--Author: Adrian Buckman
--Create Date: 17/08/2017

--Description: Produce a script that will provide ALTER statements to change the database
--ownership to the new owner and also ALTER statements to revert back to the old owner

--Revision History:
.

**********************************************/

CREATE PROCEDURE sp_ChangeDatabaseOwnerShip
(
@DBOwner NVARCHAR(128) = NULL,
@Help BIT = 0
)
AS

IF @Help = 1
BEGIN
PRINT 'Parameters:
@DBOwner NVARCHAR(128) - Set the new owner name here'
END

IF @Help = 0
BEGIN
DECLARE @UserSid VARBINARY = SUSER_SID(@DBOwner)

IF @UserSid IS NOT NULL
BEGIN

SELECT DISTINCT
sys.databases.Name AS Databasename,
COALESCE(SUSER_SNAME(sys.Databases.owner_sid),'') AS CurrentOwner,
'ALTER AUTHORIZATION ON DATABASE::['+sys.Databases.Name +'] TO ['+@DBOwner+'];' AS ChangeToNewOwner,
'ALTER AUTHORIZATION ON DATABASE::['+sys.Databases.Name +'] TO ['+COALESCE(SUSER_SNAME(sys.Databases.owner_sid),'')+'];' AS RevertToOriginalOwner
FROM
sys.databases
LEFT JOIN Sys.availability_databases_cluster ADC ON sys.databases.name = ADC.database_name
LEFT JOIN sys.dm_hadr_availability_group_states st ON ST.group_id = ADC.group_id
LEFT JOIN master.sys.availability_groups ag ON ST.group_id = AG.group_id
WHERE (primary_replica = @@Servername
AND sys.Databases.owner_sid != @UserSid)
OR (sys.Databases.owner_sid != @UserSid
AND sys.Databases.State = 0
AND sys.Databases.Source_Database_id IS NULL
AND sys.databases.Replica_id IS NULL)

END
ELSE
BEGIN
RAISERROR('No SID found for the owner name you have provided - please check the owner name and try again',11,1)
END

END

 

Thanks for reading.