Working with security

--Step:1 create a login to connect instance

USE [master]

GO

CREATE LOGIN [sql_connect] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

--Step:2  create a user and grant database level role

USE [abcd]

GO

CREATE USER [sql_connect] FOR LOGIN [sql_connect]

--Adding a db level role

ALTER ROLE [db_owner] ADD MEMBER [sql_connect]
GO

USE  [HumanResource]
GO
CREATE USER [sql_connect] FOR LOGIN [sql_connect]

GO

--Step :3 Grant database level permission

USE [HumanResource]

GO

GRANT BACKUP DATABASE TO [sql_connect]

GRANT BACKUP LOG TO [sql_connect]

GO

--Step:4 Grant permission on object level

USE [HumanResource]

GO

GRANT INSERT ON [dbo].[EMPLOYEE] TO [sql_connect]

GRANT SELECT ON [dbo].[EMPLOYEE] TO [sql_connect]

DENY DELETE ON [dbo].[EMPLOYEE] TO [sql_connect]

DENY UPDATE ON [dbo].[EMPLOYEE] TO [sql_connect]

GO

--Step:5 Granting schema level permission

USE [HumanResource]

GO

GRANT SELECT ON SCHEMA::[hr] TO [sql_connect] WITH GRANT OPTION 

GO

------------------------ To check permissions------------------

sp_helprotect --To check all permissions

GO

sp_helprotect [dbo.EMPLOYEE] --To check particular object permissions

GO 

sp_helprotect [dbo.EMPLOYEE],[sql_connect]--To check particular object and an user permissions

GO

sp_helprotect null,[sql_connect]--To check particular user permissions

GO

--using dmv

SELECT * FROM sys.database_permissions

WHERE grantee_principal_id=(SELECT principal_id FROM sys.database_principals WHERE name='sql_connect')