SQL Server Database Security

Transferred from: http://www.cnblogs.com/hoojo/archive/2011/07/20/2111990.html

--Create a login user
--create login login_name from windows with Default_database = Database | Default_language = language;
Create login [localhost\hoojo-pc] from windows with default_database = Testhome;
--Create a login user
--create login login_name with password = ' pwd ' [hashed] [Must_change] [check_expiration] [Check_policy]
Create login admin with password = ' 123456 ', default_database = Testhome;

--View Login user
SELECT * from Sys.sql_logins;

--Modify User
ALTER LOGIN admin with name = Jackson;
ALTER LOGIN to jackson with password = ' ABCD ';

--Disable, enable user
ALTER LOGIN to Jackson disable; --Disable
ALTER LOGIN to Jackson enable; --Enable

--Delete User
Drop Login to Jackson;

--Modify the mapping credentials (map login Macraes to credential Custodian04)
ALTER login Jackson with credential = Custodian04;

-------Database User
--Create a database user
Create user jack from login admin;
Create user Jason for login Jackson;

Create user jack from the login admin with default_schema = Temp_schema;

--Start the guest user (not recommended)
--Special user dbo, guest;sa corresponding dbo user, other users not mapped is the guest user
Grant connect to Guest;

--Modify User
Alter user jack with name = Jason;
Alter user Jason with default_schema = Jason_schema;

--Delete User
Drop user Jason;

--------Database Roles
--Empowering Jack users to buyers roles
Create role buyers authorIzation Jack;

--Modifying roles
Alter role buyers with NAME = New_buyers;

--Remove roles
Drop role new_buyers;

--------Architecture Management
--Create
Create schema Temp_schema;

--Specify user
Create schema Jason_scheam authorization Jason;

--Authorization inquiry
Grant Select to Jason;

--Delete
Drop schema Jason_scheam;

--------Permissions
--Authorization to create table
Grant CREATE table to Jason;
--Authorize Jason to inquire student form
Grant SELECT on student to Jason;

--Revoke permissions
Revoke CREATE table to Jason;
Revoke select on student to Jason;

SQL Server Database Security