functions/public/Publish-DatabaseRole.ps1
|
function Publish-DatabaseRole { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string] $databaseName, [Parameter(Mandatory = $true)] [string] $user, [Parameter(Mandatory = $true)] [string] $sqlServerAddress, [Parameter(Mandatory = $true)] [array] $databaseRoles ) forEach ($role in $databaseRoles) { $roleName = $role.name Write-DosMessage -Level "Information" -Message "Verifying/Creating role for $roleName for $databaseName on $sqlServerAddress server" $query = "DECLARE @cmd nvarchar(max) IF DATABASE_PRINCIPAL_ID(@roleName) IS NULL BEGIN print '-- Creating role '; SET @cmd = N'CREATE ROLE ' + quotename(@roleName); EXEC(@cmd); END" $parameters = @{roleName = $roleName} Invoke-SqlCommand -SqlServerAddress $sqlServerAddress -DatabaseName $databaseName -Query $query -Parameters $parameters if ($role.permissions.Keys.length -ge 1) { $query = "" foreach ($entity in $role.permissions.Keys) { $access = $role.permissions[$entity] Write-DosMessage -Level "Information" -Message "Verifying/Creating permission $access $entity for role $roleName" $query = "$($query)GRANT $access ON $entity TO $roleName;`n" } Invoke-SqlCommand -SqlServerAddress $sqlServerAddress -DatabaseName $databaseName -Query $query } Write-DosMessage -Level "Information" -Message "Verifying/Creating login for $user on $sqlServerAddress server" $query = "DECLARE @cmd nvarchar(max) DECLARE @escapeQuoteUser varchar(max) SET @escapeQuoteUser = replace(@user, '''', '''''') IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE sid = suser_sid(@user)) BEGIN print '-- Creating login ' + @user; SET @cmd = N'CREATE LOGIN ' + quotename(@escapeQuoteUser) + N' FROM WINDOWS'; EXEC(@cmd); END " $parameters = @{User = $user} Invoke-SqlCommand -SqlServerAddress $sqlServerAddress -DatabaseName $databaseName -Query $query -Parameters $parameters Write-DosMessage -Level "Information" -Message "Verifying/Creating login for $user on $sqlServerAddress on database $databaseName" $query = "DECLARE @cmd nvarchar(max) DECLARE @escapeQuoteUser varchar(max) SET @escapeQuoteUser = replace(@user, '''', '''''') IF(NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = @user)) BEGIN print ''-- Creating user''; SET @cmd = N'CREATE USER ' + quotename(@escapeQuoteUser) + N' FOR LOGIN ' + quotename(@escapeQuoteUser); EXEC(@cmd); END" $parameters = @{User = $user} Invoke-SqlCommand -SqlServerAddress $sqlServerAddress -DatabaseName $databaseName -Query $query -Parameters $parameters Write-DosMessage -Level "Information" -Message "Adding $user to $roleName on $sqlServerAddress" $query = "DECLARE @cmd nvarchar(max) DECLARE @exists int DECLARE @escapeQuoteUser varchar(max) SET @escapeQuoteUser = replace(@user, '''', '''''') SELECT @exists = IS_ROLEMEMBER (@roleName, @escapeQuoteUser) IF (@exists IS NULL OR @exists = 0) BEGIN print '-- Adding user to role ' + @roleName SET @cmd = N'ALTER ROLE ' + @roleName + N' ADD MEMBER ' + quotename(@escapeQuoteUser) EXEC(@cmd) END" $parameters = @{roleName = $roleName; User = $user} Invoke-SqlCommand -SqlServerAddress $sqlServerAddress -DatabaseName $databaseName -Query $query -Parameters $parameters } } |