Create login accounts and database users

Create roles, users, and permissions

/* -- Example description
In the database pubs, create a role r_test with all permissions for table jobs and select permissions for table titles.
Then a login l_test is created, and the user account u_test is created for login l_test in the database pubs.
At the same time, add user account u_test to role r_test so that it can obtain the same permissions as role r_test through permission inheritance.
Finally, the Select permission for table titles in user account u_test is denied by using the deny statement.
After using l_test to log on to the SQL server instance, it only has all permissions for table jobs.
--*/

Use pubs

-- 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, set password to PWD, default database to pubs
Exec sp_addlogin 'l _ test', 'pwd', 'pubs'

-- Add the security account u_test to the database pubs for login l_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'

-- Deny the select permission of the security account u_test on the titles table
Deny select on titles to u_test

/* -- After completing the preceding steps, log on with l_test to perform all operations on the jobs table, but cannot query the titles table. Although the role r_test has the select permission on the titles table, however, the Select permission for titles has been explicitly denied in the security account, so l_test has no select permission for the titles table --*/

-- Delete a security account from the database pubs
Exec sp_revokedbaccess 'U _ Test'

-- Delete login l_test
Exec sp_droplogin 'l _ Test'

-- Delete role r_test
Exec sp_droprole 'r _ Test'