Need to give a user full control (dbo/db_owner) across every database in your SQL Server? Here’s how you can do it quickly using PowerShell and dbatools.
Why db_owner?
Adding a user to the db_owner role in each database gives them broad permissions to manage all aspects of those databases—ideal for trusted developers or DBAs in non-production environments.
Quick Steps with dbatools
- Make sure the login exists at the server level:
New-DbaLogin -SqlInstance "YourInstance" -Login "YourUser"
- Loop through all databases and assign db_owner:
$instance = "YourInstance"
$login = "YourUser"
Get-DbaDatabase -SqlInstance $instance | Where-Object { -not $_.IsSystemObject } | ForEach-Object {
New-DbaDbUser -SqlInstance $instance -Database $_.Name -Login $login -User $login -Force
Add-DbaDbRoleMember -SqlInstance $instance -Database $_.Name -Role "db_owner" -User $login
}
- This script creates the user in each database (if needed) and adds them to the
db_ownerrole.
T-SQL Alternative
You can also use T-SQL:
USE master;
GO
DECLARE @DatabaseName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @User NVARCHAR(128)
SET @User = 'YourUser'
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4 -- Exclude system DBs
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE [' + @DatabaseName + ']; ' +
'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + @User + ''') ' +
'CREATE USER [' + @User + '] FOR LOGIN [' + @User + ']; ' +
'EXEC sp_addrolemember N''db_owner'', [' + @User + '];'
EXEC sp_executesql @SQL
FETCH NEXT FROM db_cursor INTO @DatabaseName
END
CLOSE db_cursor
DEALLOCATE db_cursor
Database Blog 