--step1: Creating login
create login Azhar with password='Hyd@1234'
--Windows login
--create login [optimize\Karl] from windows
--To check SID of Azhar
sp_helplogins Azhar
go
--step2: Creating user for Azhar
use AdventureWorks2014
go
create user Azhar for login Azhar
--To chec user info
sp_helpuser Azhar
--step3: Granting permissions
grant select,insert on Person.Address to Azhar --obj level
with grant option
grant select on schema::Sales to Azhar   --schema
deny select on Sales.CreditCard to Azhar
grant backup database to Azhar     --db level
revoke insert on Person.Address from Azhar
--To grant column level
grant select on HumanResources.Employee(BusinessEntityID,LoginID,JobTitle)
to Azhar
--step4: To check permissions
sp_helprotect --To check all permissions
go
sp_helprotect [Person.Address]
go
sp_helprotect [Person.Address],Azhar
go
sp_helprotect null,Azhar
--Using views
select * from sys.database_permissions
where grantee_principal_id=(select principal_id from sys.database_principals
where name='Azhar')
--To check the schema name
select * from sys.schemas where schema_id=9