Create a database user

To create a database user for MS SQL Server, you can either use the SQL Server Management Studio on the server or remotely on your Desktop, or you can use the SQL command line program

sqlcmd

on the server directly.

Using the SQL command line tool you need to connect to the relevant database as database administrator (sa):

sqlcmd -d <database name> -U sa


For both programs, you can create a valid database user using the following commands:

CREATE LOGIN [<DB User>] WITH PASSWORD=N'<some password>', DEFAULT_DATABASE=[<DB Name>], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE USER [<DB User>] FOR LOGIN [<DB User>]

ALTER USER [<DB User>] WITH DEFAULT_SCHEMA=[<Schema>]

CREATE SCHEMA [<Schema>] AUTHORIZATION [<DB User>]

GRANT CREATE SCHEMA TO [<DB User>]

GRANT CREATE TABLE TO [<DB User>]

GRANT CREATE VIEW TO [<DB User>]

The database user name and schema name should be identical and always created in upper case letters.
If the user account already exists, you can delete it using the following sequence of commands:

DROP SCHEMA <schema name>

DROP USER <PPM user name>

DROP LOGIN <PPM login name>

Example

You want to create a login and user called

PPMUSER

in the existing MS SQL Server database

PPMDB

. Within the MS SQL Management Studio, open a query editor and add the following command sequence:

USE [PPMDB]

GO

DROP SCHEMA <ppmschema>;

DROP USER ppmuser;

DROP LOGIN ppmuser;

GO

USE [PPMDB]

GO

CREATE LOGIN [PPMUSER] WITH PASSWORD=N'ppmuser', DEFAULT_DATABASE=[PPMDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [PPMDB]

GO

CREATE USER [PPMUSER] FOR LOGIN [PPMUSER]

GO

USE [PPMDB]

GO

ALTER USER [PPMUSER] WITH DEFAULT_SCHEMA=[PPMUSER]

GO

USE [PPMDB]

GO

CREATE SCHEMA [PPMUSER] AUTHORIZATION [PPMUSER]

GO

use [PPMDB]

GO

GRANT CREATE SCHEMA TO [PPMUSER]

GO

use [PPMDB]

GO

GRANT CREATE TABLE TO [PPMUSER]

GO

use [PPMDB]

GO

GRANT CREATE VIEW TO [PPMUSER]

GO

The chapter Create database describes how to use Microsoft SQL Server Management Studio to create a database and database user for PPM.