There are times when DBA’s are required to export database user permissions to a file. This may be for a number of reasons. Maybe for DR purposes, for auditing, for transfer to another database or instance. Sometimes we need to create a new user with the same permissions as another user or perhaps nearly the same permissions. I was having a conversation with my good friend and MVP Cláudio Silva and we were talking about how Export-SqlUser from dbatools could help in these situations and he suggested that I blogged about it so here it is.

The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present

Cláudio wrote Export-SqlUser to solve a problem. You should always start with Get-Help whenever you are starting to use a new PowerShell command

Get-Help Export-SqlUser -ShowWindow

The command exports users creation and its permissions to a T-SQL file or host. Export includes user, create and add to role(s), database level permissions, object level permissions and also the Create Role statements for any roles, although the script does not create IF NOT EXISTS statements which would be an improvement. It also excludes the system databases so if you are scripting users who need access to those databases then that needs to be considered. Cláudio is aware of these and is looking at improving the code to remove those limitations.

• SqlInstance

  The SQL Server instance name. SQL Server 2000 and above supported.

• User

  Export only the specified database user(s). If not specified will export all users from the database(s)

• DestinationVersion

  Which SQL version the script should be generated using. If not specified will use the current database compatibility level

• FilePath

  The filepath to write to export the T-SQL.

• SqlCredential

  Allows you to login to servers using alternative credentials

• NoClobber

  Do not overwrite the file

• Append

  Append to the file

• Databases

  Not in the help but a dynamic parameter allowing you to specify one or many databases

Lets take a look at it in action

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Users.sql
Notepad C:\temp\SQL2016N2-Users.sql

Lets take a look at a single database

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Fadetoblack.sql -Databases Fadetoblack
notepad C:\temp\SQL2016N2-Fadetoblack.sql

This is so cool and so easy. It is possible to do this in T-SQL. I found this script on SQLServerCentral for example which is 262 lines and would then require some mouse action to save to a file

We can look at a single user as well. Lets see what Lars Ulrich can see on the FadeToBlack database

USE [FadetoBlack]
GO
CREATE USER [UlrichLars] FOR LOGIN [UlrichLars] WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT CONNECT TO [UlrichLars]
GO
DENY INSERT ON [dbo].[Finances] TO [UlrichLars]
GO
DENY SELECT ON [dbo].[RealFinances] TO [UlrichLars]
GO
GRANT SELECT ON [dbo].[Finances] TO [UlrichLars]
GO

So he can select data from the Finances table but cannot insert and cannot read the RealFinances data. Now lets suppose a new manager comes in and he wants to be able to look at the data in this database. As the manager though he wants to be able to read the RealFinances table  and insert into the Finances table. He requests that we add those permissions to the database. We can create the T-SQL for Lars user and then do a find and replace for UlrichLars with TheManager , DENY INSERT ON [dbo].[Finances] with GRANT INSERT ON [dbo].[Finances] and DENY SELECT ON [dbo].[RealFinances] with GRANT SELECT ON [dbo].[RealFinances] and save to a new file.

$LarsPermsFile = 'C:\temp\SQL2016N2-Lars-Fadetoblack.sql'
$ManagerPermsFile = 'C:\temp\SQL2016N2-Manager-Fadetoblack.sql'
Export-SqlUser -SqlInstance SQL2016N2 -FilePath $LarsPermsFile -User UlrichLars -Databases Fadetoblack
$ManagerPerms = Get-Content $LarsPermsFile
## replace permissions
$ManagerPerms = $ManagerPerms.Replace('DENY INSERT ON [dbo].[Finances]','GRANT INSERT ON [dbo].[Finances]')
$ManagerPerms = $ManagerPerms.Replace('DENY SELECT ON [dbo].[RealFinances]','GRANT SELECT ON [dbo].[RealFinances]')
$ManagerPerms = $ManagerPerms.Replace('UlrichLars','TheManager')
Set-Content -path $ManagerPermsFile -Value $ManagerPerms

I will open this in Visual Studio Code Insiders using

code-insiders $LarsPermsFile , $ManagerPermsFile

if you are not using the insiders preview remove the “-insiders”

You can right click on the Lars file and click select for compare and then right click on the Managers file and select compare with Lars File and get a nice colour coded diff

Perfect, we can run that code and complete the request. When we impersonate Lars we get

but when we run as the manager we get

Excellent! All is well.

It turns out that there is another Fadetoblack database on a SQL2000 instance which for reasons lost in time never had its data imported into the newer database. It is still used for reporting purposes. The manager needs to have the same permissions as on the SQL2016N2 instance. Obviously the T-SQL we have just created will not work as that syntax did not exist for SQL 2000 but Cláudio has thought of that too. We can use the DestinationVersion parameter to create the SQL2000 (2005,2008/20008R2,2012,2014,2016) code

We just run

Export-SqlUser -SqlInstance SQL2016N2 -Databases FadetoBlack -User TheManager  -FilePath C:\temp\S
QL2016N2-Manager-2000.sql  -DestinationVersion SQLServer2000
Notepad C:\temp\SQL2016N2-Manager-2000.sql

and our SQL2000 compatible code is created

Simply awesome. Thank you Cláudio

 Get-Module dbatools

 Update-Module dbatools

Install-Module dbatools

Update-dbatools