SQL Script for Changing Database Ownership to ‘sa’ for all User Databases

[Technical Editor]

This SQL script is designed to change the ownership of all user databases in a SQL Server instance to the ‘sa’ (system administrator) account. It’s useful for database administrators who need to standardize database ownership or reset it for any reason.

SQL Script for Changing Database Ownership to ‘sa’

This script changes the ownership of all user databases to the ‘sa’ account:

        
	declare @sql varchar(200), @name varchar(200)
		declare cur cursor for
			select name from sys.databases
			where name not in ('master', 'tempdb', 'model', 'msdb')
			open cur
				fetch next from cur into @name
				while @@FETCH_STATUS = 0
				begin
					set @sql = 'ALTER AUTHORIZATION ON DATABASE::' + @name + ' TO [sa];'
					print @sql
					exec(@sql)
					fetch next from cur into @name
				end
		close cur
	deallocate cur
GO
        
    

Let’s break down what each part of the script does:

1. Declaring Variables:
   • @sql: This variable will store the dynamically generated SQL command for each database.
   • @name: This variable will hold the name of each database as the cursor processes them.
2. Setting up a Cursor:
   • A cursor named cur is declared to iterate through the names of all databases, excluding the system databases (‘master’, ‘tempdb’, ‘model’, ‘msdb’).
3. Opening the Cursor and Fetching Data:
   • The cursor is opened, and the first database name is fetched into the @name variable.
4. Iterating through the Databases:
   • The WHILE @@FETCH_STATUS = 0 loop ensures that the script processes each database returned by the cursor.
   • For each database, the script constructs an SQL command to change its ownership to the ‘sa’ account using ALTER AUTHORIZATION.
   • The constructed SQL command is both printed (using PRINT) for logging or debugging purposes and executed (using EXEC).
5. Closing and Deallocating the Cursor:
   • Once all databases have been processed, the cursor is closed and deallocated.
6. End of Script:
   • The GO command signifies the end of the batch in SQL Server.

Usage:

• This script is particularly useful when you need to reassign database ownership to the ‘sa’ account in bulk, perhaps as part of a maintenance routine or following a policy change in database management.

Important Notes:

• Permissions Required: The user executing this script needs to have sufficient permissions to alter database authorization and to iterate through the databases.
• Use with Caution: Changing database ownership can have security and access implications. It should be done cautiously and typically in a controlled environment.
• System Databases: The script specifically excludes system databases to prevent any unintended changes to these critical databases.
• ‘sa’ Account: The ‘sa’ account is a high-privilege account. Assigning databases to this account should align with your organization’s security policies.

As always, it’s advisable to test scripts like this in a non-production environment before running them on your production server.

[Author]

Posts