<# .Description This is a script to create a dpm user for exisiting and future databases in SQL Server. .Parameter AdminUser Specifies the system administrator account. .Parameter DpmUser Specifies the dpm user that will be created by the admin user account. .Parameter DataSource Specifies the name or network address of the instance of SQL Server to connect to. .Parameter Database Specifies the name of the database associated with the connection. #> param ( [Parameter(HelpMessage = "Enter the system administrator account credential")] [ValidateNotNull()] [Alias("u")] [System.Management.Automation.PSCredential] [System.Management.Automation.Credential()] $AdminUser, [Parameter(HelpMessage = "Enter the dpm user account account credential")] [ValidateNotNull()] [System.Management.Automation.PSCredential] [System.Management.Automation.Credential()] $DpmUser = 'dpm', [Parameter(Mandatory, HelpMessage = "Enter the data source for the admin user to connect to the database")] [ValidateNotNullOrEmpty()] [string] $DataSource, [Parameter(HelpMessage = "Enter the database name for the admin user to connect")] [ValidateNotNullOrEmpty()] [string] $Database = 'master') write-host "Begin user creation script" $sqlScript = @" DECLARE @UnsupportedEdition bit SELECT @UnsupportedEdition = CASE WHEN SERVERPROPERTY('EngineEdition') IN (5, 6, 9, 11) THEN 1 ELSE 0 END IF (@UnsupportedEdition = 1) BEGIN DECLARE @Error nvarchar(1000) SET @Error = 'This script cannot be executed on ' + CAST(SERVERPROPERTY('Edition') as nvarchar(48)) + '.'; THROW 50001, @Error, 0 END DECLARE @QuotedUser nvarchar(130) = QUOTENAME(@dpm_user); IF EXISTS(SELECT TOP 1 [name] FROM [sys].[syslogins] WHERE [name] = @dpm_user) BEGIN DECLARE @ErrorMessage nvarchar(1000) SET @ErrorMessage = 'A login for ' + @QuotedUser + ' already exists. Please remove the login and all its dependencies before attempting to execute this script again.'; THROW 50001, @ErrorMessage, 1 END DECLARE @sql nvarchar(1024) BEGIN TRY BEGIN TRANSACTION SET @sql = N'CREATE LOGIN ' + @QuotedUser + ' WITH PASSWORD = ' + QUOTENAME(@dpm_pass, '''') EXEC(@sql) IF (@@ERROR <> 0) RETURN; SET @sql = N' CREATE USER ' + @QuotedUser + ' FOR LOGIN ' + @QuotedUser + ' GRANT CONNECT SQL to ' + @QuotedUser + ' GRANT VIEW SERVER STATE to ' + @QuotedUser + ' GRANT VIEW DATABASE STATE to ' + @QuotedUser + ' USE [model]; CREATE USER ' + @QuotedUser + ' FOR LOGIN ' + @QuotedUser + ';' EXEC(@sql) IF (@@ERROR <> 0) RETURN; DECLARE @DBName nvarchar(128) DECLARE dbName_cursor CURSOR FOR SELECT [name] FROM [sys].[databases] WHERE name NOT IN ('master','model','msdb','tempdb') OPEN dbName_cursor FETCH NEXT FROM dbName_cursor INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'USE ' + QUOTENAME(@DBName) + '; CREATE USER ' + @QuotedUser + ' FOR LOGIN ' + @QuotedUser + ';' EXEC(@sql) IF (@@ERROR <> 0) RETURN; FETCH NEXT FROM dbName_cursor INTO @DBName END CLOSE dbName_cursor DEALLOCATE dbName_cursor COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION END; THROW END CATCH "@ $adminCredential = $AdminUser if ($adminCredential -eq $null -or $adminCredential -eq [System.Management.Automation.PSCredential]::Empty) { $adminCredential = get-credential -Message "A system administrator credential is required to connect to the '$Database' database on data source '$DataSource'" } $adminCredential.Password.MakeReadOnly() $dpmUserCredential = $DpmUser if ($dpmUserCredential -eq $null -or $dpmUserCredential -eq [System.Management.Automation.PSCredential]::Empty) { $dpmUserCredential = get-credential -Message "Please enter the credential for the DPM User that will be created" } $dpmUserCredential.Password.MakeReadOnly() write-host "Creating connection string with user " -NoNewline write-host "$($adminCredential.UserName)" -ForegroundColor Blue -NoNewline write-host " on the " -NoNewline write-host $Database -ForegroundColor Blue -NoNewline write-host " database on data source " -NoNewline write-host $DataSource -ForegroundColor Blue # SqlConnectionStringBuilder does not take in a SecureString $unsecuredAdminPassword = (New-Object PSCredential "user", $adminCredential.Password).GetNetworkCredential().Password $connectionStringBuilder = new-object System.Data.SqlClient.SqlConnectionStringBuilder $connectionStringBuilder.set_DataSource($DataSource) $connectionStringBuilder.set_InitialCatalog($Database) $connectionStringBuilder.set_UserID($adminCredential.UserName) $connectionStringBuilder.set_Password($unsecuredAdminPassword) $connectionString = $connectionStringBuilder.ToString() write-host "Creating connection" $sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString) write-host "Running sql script to create user " -NoNewline write-host $dpmUserCredential.UserName -ForegroundColor Blue try { $sqlCommand = new-object System.Data.SqlClient.SqlCommand($sqlScript, $sqlConnection) $unsecuredDpmUserPassword = (New-Object PSCredential "user", $dpmUserCredential.Password).GetNetworkCredential().Password $sqlCommand.Parameters.AddWithValue("@dpm_user", $dpmUserCredential.UserName) | out-null $sqlCommand.Parameters.AddWithValue("@dpm_pass", $unsecuredDpmUserPassword) | out-null $sqlConnection.Open() $rowsAffected = $sqlCommand.ExecuteNonQuery() write-host "Successfully ran user creation sql script" -ForegroundColor Green } catch { write-error $Error[0].Exception.Message } finally { $sqlConnection.Close() } write-host "User creation script has completed"