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"
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