dbatools & PowerShell – Page 3

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

Backup and Restore a SQL database with a two line script

Here is a simple method to backup and restore a database from Server A to Server B using dbatools. The script will backup DB1 from Server A and restore the backup onto Server B as DB2.

Please note that the SQL service account on both servers need to have access to the network shared folder in order for this to work.

Backup script:

Backup-DbaDatabase -sqlinstance ServerA -database DB1 -path \\NetworkShare\SQLBackup\ServerA -copyonly -compressbackup

Restore script:

Restore-DbaDatabase -SqlInstance ServerB -Path "\\NetworkShare\SQLBackup\ServerA\DB1_202201261123.bak" -UseDestinationDefaultDirectories -DatabaseName DB2 -ReplaceDbNameInFile

However, if you ever need to simply copy a database DB1 from Server A to Server B. You can use the below script:

$ss = 'ServerA'
$ds = 'ServerB'

# Copy one database
Copy-DbaDatabase -Source $ss -Destination $ds -BackupRestore -Database DB1 -SharedPath \\NetworkShare\SQLBackup\ServerA

If you need to do a full-on migration of databases from Server A to Server B, here is the script:

$ss = 'ServerA'
$ds = 'ServerB'

# Copy all databases
Copy-DbaDatabase -Source $ss -Destination $ds -BackupRestore -Alldatabases -SharedPath \\NetworkShare\SQLBackup\ServerA

Install sql server failover cluster using dbatools

https://github.com/sqlcollaborative/dbatools/discussions/7447

Copy databases from One SQL instance to another -dbatools

Back with another quick tip to copy databases from one sql instance to another using dbatools one-line command script:

Copy-DbaDatabase -Source SQLSERVER01 -Destination SQLSERVER02 -Database STAGE_DB_Name -BackupRestore -SharedPath \\NetworkPath\SQLBackup\SQLSERVER01 -NewName PROD_DB_Name

-Source: Name of the source SQL server

-Destination: Name of the destination SQL server where you are going to restore the source DB

-BackupRestore: Will use a backup and restore methodology to copy over the database

-SharedPath: This network path should be accessible to both source and destination servers

-NewName: Use this if you want to restore the database with a different name from source

Update Service accounts for SQL server services using dbatools

Recently, I found a neat little trick using dbatools to update service accounts for SQL Server service and agent. It is generally a good security practice to run the services under a dedicated domain account. The default local system account has more privileges than necessary. Great thing about this method is that it is similar to updating service account using SQL Server Configuration Manager. The process will grant all the minimum necessary permissions to the service account and you don’t need to do a thing!

$cred = Get-Credential domain\serviceaccount
Get-DbaService -ComputerName sqlserver01 | Out-GridView -Passthru | Update-DbaServiceAccount -ServiceCredential $cred

Soon after I ran this, I got an error that the account could not be set. I was able to revert the account back to local system account and start back the services. Now I went onto investigate the root cause for the error. I also ensured that the account is not locked out, password is accurate. Finally I looked at the System logs under event view logs on the server. Bingo, now we are getting somewhere. This is the error I saw:

The MSSQLSERVER service was unable to log on as domain\ServiceAccount with the currently configured password due to the following error: 
Logon failure: the user has not been granted the requested logon type at this computer.
 
Service: MSSQLSERVER 
Domain and account: domain\ServiceAccount
 
This service account does not have the required user right "Log on as a service."
 
User Action
 
Assign "Log on as a service" to the service account on this computer. You can use Local Security Settings (Secpol.msc) to do this. If this computer is a node in a cluster, check that this user right is assigned to the Cluster service account on all nodes in the cluster.
 
If you have already assigned this user right to the service account, and the user right appears to be removed, check with your domain administrator to find out if a Group Policy object associated with this node might be removing the right.

I opened the security policy on the server (run secpol.msc) and found that the account was not present in the User policy named ” Log on as a service “. After talking to the systems engineer, I found out that the server is managed through a GPO. We had them update the GPO to include the service account in the policy. Once the GPO took effect, we were able to run the above dbatools command and set the service account successfully.

Obviously, this process of setting service account also requires that you restart the services in order for the change to take effect. Hope you enjoyed this!

Share this:

Share this:

Share this:

Share this:

Share this: