Creating database, schema, user and login with TSQL

Here is how to create a database, schema, user and login in MS SQL - this should work equally for MS SQL 2005/2008 at least. In MS SQL, there is a distinction between a USER and a LOGIN; a user is attached to databases can be assigned various roles and permissions, and a login can be mapped to different users.

CREATE DATABASE TEST;
GO
USE TEST;
GO
CREATE SCHEMA TEST;
GO
CREATE LOGIN TEST_USER WITH PASSWORD = 'password';
EXEC sp_defaultdb @loginame='TEST_USER', @defdb='TEST'
CREATE USER TEST_USER FOR LOGIN TEST_USER WITH DEFAULT_SCHEMA = TEST;
EXEC sp_addrolemember 'db_owner','TEST_USER'
GO

Microsoft's Transact SQL (or TSQL) and Oracle's PL/SQL are both proprietary procedural SQL languages that let you write programs in what is essentially SQL with control statements etc. If you put the above code into a file, you could run it with a command such as this.

sqlcmd -S "localhost\SQLEXPRESS" -U sa -P password -i "C:\path\to\script.sql"

The line EXEC sp_addrolemember 'db_owner','TEST_USER' is used to make the new user an owner of the new database. A database owner has permission to create tables, procedures and views etc within the given database. In general, a role is a collection of permissions that will be granted or denied to whichever user is assigned that role. There is an important distinction about the db_owner role in particular: an owner cannot have any permission denied to it. The ownership role is thus very powerful and should be used sparingly. In production systems it would be more advisable to create a new role with specific permission assigned or denied.

Lastly, here is how to roll it all back. Users belong to databases, so if you drop the database - and the user only belongs to that one database - you also delete the user.

-- Roll it back..
DROP DATABASE TEST;
DROP SCHEMA TEST;
DROP LOGIN TEST_USER;
GO