sql server create user and grant permission

Create Login, User, assign Permission: SQL Server Tutorial 

---LOGIN  IS THE 3 STEPS PROCESS

---CREATE LOGINS

--- CREATE USERS

--- SET PERMISSION

---PUBLIC ( CONNECT & VIEW ANY DATABASE )

--PUBLIC ROLES (CONNECT TO DB PERMISSION)

--create logins

CREATE LOGIN BANSH WITH PASSWORD='Bnash@123'

---chk login info

sp_helplogins 'BANSH'

---create user for login

use AdventureWorks2012

create useR BANSH FOR LOGIN BANSH

---chk USER info

sp_helpUSER 'BANSH'

---set GRANT permission

GRANT SELECT,INSERT ON PERSON.ADDRESS TO BANSH --OTJ LEVEL

WITH GRANT OPTION---(OPTIONAL)

GRANT SELECT ON SCHEMA::SALES TO BANSH  ----SCHEAM LEVEL PERMISSION

---RESTRICET PERMISSION ON SALES SCHEMA SOME TABLES 

DENY SELECT ON SALES.CreditCard TO BANSH

---TAKE BACK PERMISSION 

REVOKE INSERT ON PERSON.ADDRESS from BANSH

--Msg 4611, Level 16, State 1, Line 1

--To revoke or deny grantable privileges, specify the CASCADE option.

--Note if we are using with grant option then give the erro rest of case its working f9 

--soln

REVOKE INSERT ON PERSON.ADDRESS from BANSH CASCADE

--set db level permission 

grant backup database to BANSH

 

---GRANT PERMISSION COLUMN LEVEL ON A TABLE 

GRANT SELECT ON HumanResources.Employee(BusinessEntityID,LoginID,JobTitle,CurrentFlag) to bansh

GRANT SELECT,update ON HumanResources.Employee(BusinessEntityID,LoginID,JobTitle,CurrentFlag) to bansh

--chk permission current db user permision

sp_helprotect

--chk permission table level   user permision

sp_helprotect [Person.Address]

sp_helprotect [HumanResources.Employee]

--chk the user permission on a table

-

sp_helprotect [HumanResources.Employee],bansh

sp_helprotect [Person.Address],bansh

--chk how many table/schema hv tht user permission

sp_helprotect null ,bansh

---

--using views to chk permission

select * from sys.database_permissions

where grantee_principal_id=(select  principal_id from sys.database_principals

where name='bansh')

--to chk schema names--

select * from sys.schemas where schema_id='9'

use [AdventureWorks2012]

GO

GRANT SELECT TO [OPODBA]

GO

use [AdventureWorks2012]

GO

GRANT SELECT ON SCHEMA::[HumanResources] TO [OPODBA]

GO