Use a T-SQL script to create a role in SQL Server and grant the role the appropriate Permissions

-- Add a role, grant permissions to the role, and then add the user to the role.

-- Create role r_test
EXEC sp_addrole 'r _ Test'

-- Grant r_test all permissions on the jobs table
Grant all on jobs TO r_test
-- Grant the role r_test the SELECT permission on the titles table.
Grant select on titles TO r_test

-- Add login l_test and set the password to pwd. The default value is Database Pubs
EXEC sp_addlogin 'l _ test', 'pwd', 'pubs'

-- Add to database pubs for login l_test Security Account u_test
EXEC sp_grantdbaccess 'l _ test', 'U _ Test'

-- Add u_test as a member of role r_test
EXEC sp_addrolemember 'r _ test', 'U _ Test'