Object Permissions in SQL Server

Objective: To explore certain syntax for giving permissions in SQL Server Objects:

DATABASE PERMISSIONS FOR USER:

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_accessadmin', N'USERNAME'

GO

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_backupoperator', N'USERNAME'

GO

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_denydatareader', N'USERNAME'

GO

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_denydatawriter', N'USERNAME'

GO

USE [DB NAME]

GO

EXEC sp_addrolemember N'db_securityadmin', N'USERNAME'

GO

STORED PROCEDURE PERMISSION:

GRANT ALTER ON [dbo].[SP_NAME] TO [USER NAME]

GO

GRANT CONTROL ON [dbo].[SP_NAME] TO [USER NAME]

GO

GRANT TAKE OWNERSHIP ON [dbo].[SP_NAME] TO [USER NAME]

GO

GRANT EXECUTE ON [dbo].[SP_NAME] TO ANSON

GO

GRANT VIEW DEFINITION ON [dbo].[SP_NAME] TO [USER NAME]

GO

GRANT EXECUTE ON employee_proc TO public

TABLE LEVEL PERMISSIONS:

GRANT SELECT ON TableName TO Alex

DENY SELECT ON TableName TO Alex

REVOKE SELECT ON TableName TO Alex

DENY SELECT ON TableName TO Sales, Tech - HERE WE CAN DENY FOR TWO ROLES

DENY UPDATE, DELETE, INSERT ON TableName TO Accounting

Also refer the below articles:

1. Database Journal

2. searchsqlserver.techtarget.com

3. MSSQL Tips