SQL Server Logins, Users and Permissions

SQL Server Logins, Users and Permissions

step1: Creating login
create login qader with password='Hyd@1234'
Windows login
create login [optimize\Karl] from windows
To check SID of qader
sp_helplogins qader
go

step2: Creating user for qader
use AdventureWorks2014
go
create user qader for login qader
To chec user info
sp_helpuser qader

step3: Granting permissions
grant select,insert on Person.Address to qader obj level
with grant option
grant select on schema::Sales to qader   schema
deny select on Sales.CreditCard to qader
grant backup database to qader     db level
revoke insert on Person.Address from qader
To grant column level
grant select on HumanResources.Employee(BusinessEntityID,LoginID,JobTitle) to qader

step4: To check permissions
sp_helprotect To check all permissions
go
sp_helprotect [Person.Address]
go
sp_helprotect [Person.Address],qader
go
sp_helprotect null,qader
Using views
select * from sys.database_permissions
where grantee_principal_id=(select principal_id from sys.database_principals
where name='qader')
To check the schema name
select * from sys.schemas where schema_id=9