Authenticated Login

Create Login

Using TSQL

Syntax
CREATE LOGIN login_name WITH PASSWORD = {'password'}, 
	DEFAULT_DATABASE = {database}
Example
USE master;
CREATE LOGIN cse WITH PASSWORD = '123', DEFAULT_DATABASE = webcse;

Create Login

Using Stored Procedure

Syntax
EXEC sp_addlogin @loginame = 'login', @passwd = 'password', 
	@defdb = 'database';
Example
USE master;
EXEC sp_addlogin @loginame = 'cse', @passwd = '123', 
	@defdb = 'webcse';
EXEC sp_addlogin 'cse', '123', 'webcse';

Drop Login

Using TSQL

Syntax
DROP LOGIN login_name
Example
USE master;
DROP LOGIN cse;

Drop Login

Using Stored Procedure

Syntax
EXEC sp_droplogin @loginame = 'login'
Example
EXEC sp_droplogin @loginame = 'cse';
EXEC sp_droplogin 'cse';

Create User

Using TSQL

Syntax
CREATE USER user_name FOR LOGIN login_name
Example
USE webcse;
CREATE USER cse FOR LOGIN cse;

Create User

Using Stored Procedure

Syntax
EXEC sp_adduser @loginame = 'login', @name_in_db = 'user', 
	@grpname = 'role';
Example
EXEC sp_adduser @loginame = 'cse', @name_in_db = 'cse';
or
EXEC sp_adduser 'cse', 'cse';
or
EXEC sp_adduser 'cse';

Drop User

Using TSQL

Syntax
DROP USER user_name;
Example
USE webcse;
DROP USER cse;

Drop User

Using Stored Procedure

Syntax
EXEC sp_drop @name_in_db = 'user';
Example
USE webcse;
EXEC sp_dropuser @name_in_db = 'cse';
or
EXEC sp_dropuser 'cse';

Grant Permission to User

Syntax
GRANT <permission> ON object_name TO user;
Permission
Alter Delete Execute Insert References Select Update

Grant Permission to User

Permission Select

Select All Columns

GRANT SELECT ON Department TO cse;

Select Filter Columns

GRANT SELECT ON Employee(EmployeeId, Salary) TO cse;

Grant Permission to User

Permission Insert
GRANT INSERT ON Department TO cse;

Grant Permission to User

Permission Upate
GRANT UPDATE ON Department TO cse;

Grant Permission to User

Permission Delete
GRANT DELETE ON Department TO cse;

Grant Permission to User

Permission Insert, Update, Delete, Select
GRANT SELECT, INSERT, UPDATE, DELETE ON Department TO cse;

Grant Permission to User

Permission Execute
GRANT EXECUTE GetDepartments TO cse;

Grant Permission to User

Permission References
GRANT REFERENCES (DepartmentId) ON Department(DepartmentId) TO cse;

Revoke Permission to User

Synstax
REVOKE <permissions> ON object_name FROM user;
Revoke Permission
REVOKE SELECT ON Department FROM cse;

Create Role

Synstax

Using TSQL

CREATE ROLE role_name [AUTHORIZATION owner_name];
Example
CREATE ROLE member AUTHORIZATION cse;

Create Role

Synstax

Using Stored Procedure

EXEC sp_addrole @rolename='role', @ownername='user';
Example
EXEC sp_addrole @rolename='member', @ownername='cse';

Drop Role

Synstax

Using TSQL

DROP ROLE role_name;
Example
DROP ROLE member;

Drop Role

Synstax

Using Stored Procedure

EXEC sp_droprole @rolename='role';
Example
EXEC sp_droprole @rolename='member';

Grant Permission to Role

Synstax

Using TSQL

GRANT <permission> ON object_name TO role;
Example
GRANT SELECT ON Department TO member;

Add User in Role

Synstax

Using TSQL

ALTER ROLE role_name ADD MEMBER user;
Example
ALTER ROLE member ADD MEMBER cse;

Add User in Role

Synstax

Using Stored Procedure

EXEC sp_addrolemember @rolename = 'role', @membername = 'user';
Example
EXEC sp_addrolemember @rolename = 'member', @membername = 'cse';