Create roles, users, permissions

Create roles, users, permissions
/ * РExample example has all the permissions of the table jobs created in the pubs database the SELECT privilege role r_test titles table then create a Login l_test to, and then the database pubs Login l_test create a user account u_test the same time The user account u_test Add to the role r_test, so that it obtained permission inheritance Last the role r_test permissions using the DENY statement rejecting user account u_test SELECT privilege on table titles. After such processing, l_test to login to the SQL Server instance, it only has all the permissions of the table jobs. Р* /
USE pubs
– Role r_test create
EXEC sp_addrole ‘r_test’
– Grant all permissions r_test jobs table
GRANT ALL ON jobs TO r_test
– Grant SELECT permission the role r_test on the titles table
GRANT SELECT ON titles TO r_test
– Add Login l_test, set a password pwd, the default database for pubs
EXEC sp_addlogin ‘l_test’, ‘pwd’, ‘pubs’
– The Login l_test in the pubs database to add security account u_test
EXEC sp_grantdbaccess ‘l_test’, ‘u_test’
– The Add u_test role r_test the members of
EXEC sp_addrolemember ‘r_test’, ‘u_test’
– Denial of security account u_test SELECT permission on the titles table
DENY SELECT ON titles TO u_test
/ * – After completing the above steps, Login l_test, jobs table all, but you can not query the titles table, although the the role r_test titles table SELECT permissions, but has been explicitly rejected in the security account for the titles of select privileges, so l_test the titles table of the select permissions – * /
– Remove the security account from the pubs database
EXEC sp_revokedbaccess ‘u_test’
– Remove login l_test-
EXEC sp_droplogin ‘l_test’
– Remove character r_test-
EXEC sp_droprole ‘r_test’