UNDERCOVER TOOLBOX: sp_ChangeDatabaseOwnership – Producing statements to Alter Authorisation

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:

2017-08-17 14_03_28-SQLQuery13.sql - LAPTOP-ADRIANB_SQLDEV2.master (ADASTRA_buckmana (53))_ - Micros

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:

2017-08-17 14_06_10-SQLQuery13.sql - LAPTOP-ADRIANB_SQLDEV2.master (ADASTRA_buckmana (53))_ - Micros

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.

 

One thought on “UNDERCOVER TOOLBOX: sp_ChangeDatabaseOwnership – Producing statements to Alter Authorisation

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: