Skip to content

Create DB User Parms.sql

Jump to bottom
Phil Huhn edited this page Oct 2, 2017 · 2 revisions

Introduction

This is a simple dynamic T-SQL script to create a database and SQL user account. The focus of this script is to create a database for Project-Nami WordPress web-site on a local computer. The script is intended to be used in a development environment on a developer's workstation.

I created this for a talk I was writing. I can see this being used for quickly creating numerous web-sites to demonstrate themes for a project.

Create a website and set permissions, as follows:

  • creates a database,
  • create a login database account,
  • add a login account to database,
  • grant the above account user owner/read/write permissions to the database,
  • grant the 'site user' account owner/read/write permissions to the database.

Parameters

There are four parameters for the script

  • dbName: is a string value for the database name,
  • uName: is a string value for the SQL user account name, a value of _ can be used as an empty value,
  • uPwd: is a string value for the SQL user account password,
  • siteUser: is a string value for the user account that the web-site should run as, a value of _ can be used as an empty value.

Executions

PowerShell script

One option to execute the T-SQL script is by the Create-DB-User-Parms.ps1 PowerShell script. The following example executes the T-SQL script by the provided PowerShell script:

    PS>.\Create-DB-User-Parms.ps1 -server ".\SQLExpress" -dbName "WP01" -userName "WP01" -userPwd "!WP-AcEgIkMoQsUwY01!" -siteUser "NT AUTHORITY\IUSR"

You must navigate to the folder containing the PowerShell script. The user must have privileges to create a SQL Server database, this may require you to launch the PowerShell ISE using the Run As Administrator option.

Command line script

Another option to execute the T-SQL script is by the Create-DB-User-Parms.cmd command line script. The following example executes the T-SQL script by the provided command script:

    C:\ProjectNami-Windows-Install-Utils-master> Create-DB-User-Parms .\SQLExpress .\log.txt  WP01 WP01 "!WP-AcEgIkMoQsUwY01!" "NT AUTHORITY\IUSR"

You must navigate to the folder containing the Command line script. The user must have privileges to create a SQL Server database, this may require you to launch the windows command line prompt using the Run As Administrator option.

SSMS

Another option to execute the T-SQL script is to load the Create-DB-User-Parms.sql script into SSMS and change the parameters to the desired values as follows:

    DECLARE
       @dbName	VARCHAR(50) = '$(dbName)',	-- database name
       @uName	VARCHAR(50) = '$(uName)',	-- user name
       @uPwd	VARCHAR(50) = '$(uPwd)',	-- user password
       @siteUser VARCHAR(50) = '$(siteUser)', -- The user account the site should run as

change to:

    DECLARE
       @dbName	VARCHAR(50) = 'WP01',	-- database name
       @uName	VARCHAR(50) = 'WP01',	-- user name
       @uPwd	VARCHAR(50) = '!WP01-AcEgIkMoQsUwY!',	-- user password
       @siteUser VARCHAR(50) = 'NT AUTHORITY\IUSR', -- The user account the site should run as

If you use the SSMS method, I suggest you rename the script and the user must have privileges to create a SQL Server database.

Clone this wiki locally