Database add account number, add user __ Database

--1. Create the sample environment.
First use the following code to create a login l_test and create an associated user account u_test for login in the database pubs, and give the user account U_test Select permissions on table titles to implement login L_test connect to SQL Server instance, you can access table titles. An application role R_p_test is then created that grants the role the SELECT permission for table jobs to enable access to the specific table jobs when the r_p_test is activated.
Use pubs

--Create a login l_test, password pwd, default database pubs
EXEC sp_addlogin ' l_test ', ' pwd ', ' pubs '

--Add a security account for the login l_test in the database pubs u_test
EXEC sp_grantdbaccess ' l_test ', ' u_test '

--Grant the security account u_test SELECT permissions on the titles table
GRANT SELECT on titles to U_test

--Create an application role r_p_test, password ABC
EXEC sp_addapprole ' r_p_test ', ' abc '

--Grant role r_p_test SELECT permissions on the jobs table
GRANT SELECT on jobs to R_p_test
Go

--2. Activates the application role.
/*--Activation Instructions
After the sample environment is created, anywhere (such as Query Analyzer, application)
Using the login l_test to connect to an instance of SQL Server, you can access only the table titles, or the guest user
And the objects that the public role allows access to.
If you want to allow login access to the table jobs in certain specific applications, then,
You can activate the application role R_p_test, and after activating the application role, the permissions for the login itself disappear.
The following is a login in Query Analyzer to demonstrate the difference in data access before and after activating application role r_p_test.
--*/
--Before activating the application role R_p_test, the login has access to table titles, but no table jobs's access rights
SELECT Titles_count=count (*) from titles
SELECT Jobs_count=count (*) from jobs
/*--results:
Titles_count
------------
18

(The number of rows affected is 1 rows)

Server: Message 229, Level 14, State 5, line 2
The SELECT permission to object ' Jobs ' (database ' pubs ', owner ' dbo ') was rejected.
--*/
Go

--Activate the R_p_test application role with the password ABC and encrypt the password before sending it to SQL Server
EXEC sp_setapprole ' R_p_test ', {Encrypt N ' abc '}, ' ODBC '
Go

--After activating the application role R_p_test, the login loses the access to the table titles and gets the access rights of the table jobs
SELECT Titles_count=count (*) from titles
SELECT Jobs_count=count (*) from jobs
/*--Results
Server: Message 229, Level 14, State 5, line 2
The SELECT permission for object ' titles ' (Database ' pubs ', owner ' dbo ') was rejected.
Jobs_count
-----------
14

(The number of rows affected is 1 rows)
--*/