Creating SQL Server 2005 login and user via SQL query

Create Login with password

CREATE LOGIN app3 WITH PASSWORD='app3', DEFAULT_DATABASE=master, CHECK_POLICY=OFF

Create login with windows domain account

CREATE LOGIN [Adventure-Works\Mary5] FROM WINDOWS;

As you create a login in SQL 2005 database, it assigns a fixed server role to login. By default it assigns a Public role (VIEW ANY DATABASE PERMISSION)

To assign any other fixed server role to login, you may use following T- SQL

sp_addsrvrolemember @loginame =  'apploginname' ,  @rolename =  'processadmin'

Note: To add server level and database level permissions, you should be a member of security admin

I tried to consolidate the information on SQL server fixed role. Please see following page for details about SQL server fixed role http://aspdotnetgeek.blogspot.com/2008/07/sql-server-2005-fixed-server-roles.html

Add user mapping

Following SQL would create a user in mydatabase and assign a default_schema as DBO

USE [mydatabase]

GO

CREATE USER [applogin] FOR LOGIN [applogin]

GO

USE [mydatabase]

GO

ALTER USER [applogin] WITH DEFAULT_SCHEMA=[dbo]

GO

A user can be assigned many schemas and has one default schema

db_accessadmin: Members of the db_accessadmin fixed database role can add or remove access for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator: Members of the db_backupoperator fixed database role can backup the database.

db_datareader: Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.

db_datawriter: Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_ddladmin: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_denydatareader: Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

db_denydatawriter: Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_owner: Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database.

db_securityadmin: Members of the db_securityadmin fixed database role can modify role membership and manage permissions.

http://msdn.microsoft.com/en-us/library/ms189121.aspx