SQLServer 2005 Control User Rights Access Table Graphics Tutorial


I. demand

In the process of managing the database, we often need to control the access rights of a user to the database, such as only giving the user access to a table, or even CRUD, and access rights to a few fields with smaller granularity. Writing this article is to explain the operation process.

In fact, this is only a very simple piece of SQL Server permission management, some places do not have in-depth understanding and description, just hope to help some of the children’s shoes just started, other heroes should be: I startled in public, and then laughed. (Du Sheng)

II. Operational steps

1. First enter the database level of the [security] – [login name] – [new login name]

(Figure 1: New login name)

2. On the General tab, create the login name and set the default database as shown in the following figure

(Figure 2: Setting options)

3. On the User Mapping tab, as shown in the following figure, check the database you need to set up, set up the Architecture, and click the Confirm button to complete the operation of creating users.

(Figure 3: Selecting the corresponding database)

4. Now we can set permissions on User tables in the TestLog database, [tables] – [attributes]

(Figure 4: Select the corresponding table)

5. On the Permission tab, click Add – [Browse] – [Select Objects] as shown in the figure below.

(Figure 5: Users who set up access tables)

6. After clicking on “Confirm” above, we can find the corresponding permissions in the list below. If you want to refine the permissions to columns, there is a button in the lower right corner that can be set. Clicking on the “Confirm” button completes the setting of these permissions.

(Figure 6: Privilege List)

7. Now use the TestUser user to login to the database. After login, as shown in the figure below, you can only see one table now.

(Figure 7: Effect)


1. In Step 3 above, it should be noted that if the corresponding database is not selected here, TestUser can not be found in the TestLog database later.

(Figure 8: TestUser user not found)

2. In the third step above, after setting up the TestLog data, you need to click the [confirmation] button to complete the user creation operation. If you set up the [security object] at this time, you can’t find the newly created TestUser user in [add] – [specific object] – [object type] – [login name] [browse].

3. In fact, the user created at the database level is global. When a database, such as TestLog, is set up, the user will appear in the database’s security list. If you delete the TestLog user, the following prompt will appear. After deletion, the user will not be able to log in. Users need to be deleted from the corresponding database. If they are not deleted and created, they will report errors.

(Figure 9: Delete TestUser users)

4. In the list of explicit permissions in Step 6, if the option of Control is selected, then it is meaningless to set the query in Select, and the query will not be restricted. If column permissions are set, the following error message will normally be displayed:

(Figure 10: Effect)

5. In the TestLog database, [security] – [TestUser] – [attributes] – [security objects] – [add] – [object types], there are more object types at the database level that can be set.

(Figure 11: Other object types)