Create roles, users, and permissions

Create roles, users, permission/* -- Example: In the database pubs, create a role r_test with all permissions for table jobs and select permissions for table titles, and then create a login Rochelle test, then, create the user account u_test for login l_test in the database pubs and add the user account u_test to the role r_test, the user obtains the same permissions as role r_test through permission inheritance, and finally uses the deny statement to deny the select permission of user account u_test for table titles. 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_testexec sp_addrole 'r _ test' -- Grant r_test all permissions on the jobs table grant all on jobs to r_test -- Grant role r_test the select permission on the titles table grant select on titles to r_test -- add login l_test, set the password to PWD. The default database is pubsexec sp_addlogin 'l _ test', 'pwd ', 'pubs' -- add the security account u_testexec sp_grantdbaccess 'l _ test' to the database pubs for login l_test ', 'U _ test' -- add u_test as the member of role r_test exec sp_addrolemember 'r _ test ', 'U _ test' -- deny the select permission of the security account u_test to the titles table deny select on titles to u_test/* -- use l_test to log on after completing the preceding steps, you can 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, the Select permission on the titles table has been explicitly denied in the security account, therefore, l_test does not have the select permission for the titles table -- */-- deletes the security account exec sp_revokedbaccess 'U _ test' from the database pubs -- deletes login l_testexec sp_droplogin 'l _ test' -- deletes the role r_testexec. sp_droprole 'r _ Test'