Tag: permissions

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
%d bloggers like this: