Tag: permissions

Granting dbo Access to a User on All SQL Server Databases with dbatools

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

  1. Make sure the login exists at the server level:
New-DbaLogin -SqlInstance "YourInstance" -Login "YourUser"
  1. 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_owner role.

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

Copy permissions from one user to another using dbatools

Often, there is a request to replicate permission to a new hire or team member using an existing user. Typically, I prefer to use Active directory groups manage permissions for team members.

However, if your SQL instance has native SQL user accounts, you cant easily maintain similar permissions among team members. Here is a way to do that using dbatools. Credit goes to Claudio Silva 

$sqlInstance = "SQLSERVER01"
$existingUser = "<srcUser>"
$newUser = "<newUser>"

$ExportedUser = Export-DbaUser -SqlInstance $sqlInstance -User $existingUser -PassThru

$NewUserPermissions = $ExportedUser -replace $($existingUser -replace '\\', '\\'), $newUser

# Copy the permission to the clipboard. Paste on your query editor and paste there.
$newUserPermissions | Set-Clipboard

Minimum necessary permissions to monitor SQL server instance – Red Gate SQLMonitor

  1. The account used to monitor your SQL Server instances should have the following permissions:
  2. Member of the sysadmin role (role required for Integrity check overdue alerts (to run DBCC DBINFO) and to allow SQL Monitor to tur on the deadlock trace flag.
  3. If you are unable to grant sysadmin permissions to the account. Grant the following permissions:
  4. Member of the db_datareader role on the msdb system database.
  5. Member of the SQL_AgentReader role on the msdb system database.
  6. Member of the db_ddladmin database role on all databases (needed to run sys.dm_db_index_physical_stats() required by the Fragmented index alert).
  7. VIEW ANY DEFINITION server permission.
  8. ALTER TRACE server permissions (if you want to enable trace data).
  9. VIEW SERVER STATE and VIEW DATABASE STATE database permissions on all databases.
  10. Member of the db_owner role on the tempdb database.
  11. EXECUTE on xp_readerrorlog.
  12. Below is the script to grant the non-sysadmin permissions described above:
USE [msdb]
GO
CREATE USER [Domain\SQLServerAccount] FOR LOGIN [Domain\SQLServerAccount]
GO
USE [msdb]
GO
/*Member of the db_datareader role on the msdb system database*/
ALTER ROLE [db_datareader] ADD MEMBER [Domain\SQLServerAccount]
GO
USE [msdb]
GO
/*Member of SQLAgentReader role on the msdb system database*/
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [Domain\SQLServerAccount]
GO
USE [tempdb]
GO
CREATE USER [Domain\SQLServerAccount] FOR LOGIN [Domain\SQLServerAccount]
GO
USE [tempdb]
GO
/*Member of the db_owner  role on the tempdb database*/
ALTER ROLE [db_owner] ADD MEMBER [Domain\SQLServerAccount]
GO
use [master]
GO
/*ALTER TRACE server permission*/
GRANT ALTER TRACE TO [Domain\SQLServerAccount]
GO
use [master]
GO
/*VIEW ANY DEFINITION server permission*/
GRANT VIEW ANY DEFINITION TO [Domain\SQLServerAccount]
GO
USE master;
GRANT EXEC ON xp_readerrorlog TO [Domain\SQLServerAccount];


/* Run the output of the below script in a separate window in order to grant db_ddladmin role and view database state permission to the user*/
USE [master]
GO
DECLARE @UserName VARCHAR(25) = 'Domain\SQLServerAccount'
SELECT 'USE ['+name+'] CREATE USER ['+@UserName+'] FOR LOGIN ['+@UserName+']; ALTER ROLE db_ddladmin ADD MEMBER ['+@UserName+']; GRANT VIEW DATABASE STATE TO ['+@UserName+'];'
FROM sys.databases