SQL Server 2005 Control User Rights Access table

I. Demand

In managing a database, we often need to control the access of a user to a database, such as the right to access a table for that user, even the crud permissions, and, to a lesser extent, access to a few fields. Writing this article is a description of the operation process.

In fact, this is just the SQL Server Rights management very simple small piece, some places did not understand deeply and narrated, just hope to some just start of child shoes have help, other heroes are: I classroom startled, and then have to laugh. (ST)

Second, the Operation steps

1. First go to the database-level security-Login name-New logon name

(Figure 1: New logon name)

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

(Figure 2: Setting options)

3. On the User Mappings tab, as shown in the following illustration, tick the database you want to set up, set the schema, click the "Confirm" button, and complete the Create user action

(Figure 3: Select the corresponding database)

4. Now we can set permissions on the user table in the Testlog database, "table"-"Properties"

(Figure 4: Select the corresponding table)

5. On the Permissions tab, as shown in the following illustration, click "Add"-"Browse"-"Select Object"

(Figure 5: Set access to the table's users)

6. After clicking "Confirm" on the above, we can find the corresponding permissions in the following list, if you want to refine the permissions of the column, there is also a "Column permissions" button can be set, click the "Confirm" button to complete the set of these permissions

(Figure 6: Permissions list)

7. Use the TestUser user to log into the database now, after landing the following figure shows, now can only see a table

(Figure 7: effect)

III. Matters of note

1. Note in the 3rd step above: If the corresponding database is not selected here, then the testuser will not be found in the Testlog database.

(Figure 8: TestUser user not found)

2. In the 3rd step above, after setting up the Testlog data, you need to click the "Confirm" button to complete the Create user action, if this time to set "security object", is not in the "add"-"specific Object"-"Object Type"-"Login"-"Browse" The newly created TestUser user is found in the.

3. In fact, the database-level "security" created by the user is global, when a database is set up, such as Testlog, the user will appear in the database "security" list. If you delete testlog this user, the following prompts appear. After the deletion, the user will not be able to log in. Need to go to the corresponding database to delete users, if not deleted and created, will be an error.

(Figure 9: removing testuser users)

4. In the "Explicit Permissions" list in step 6th, if the option "control" is selected, setting query "column Permissions" in select does not make sense, and the query is not restricted. If you set column permissions, you will normally see an error message for the following illustration:

(Figure 10: effect)

5. In the Testlog database, "Security"-"TestUser"-"Properties"-"Security Objects"-"Add"-"Object type" Here are some of the object types you can set on the database level.

(Figure 11: Other object types)

(Source: http://gaizai.cnblogs.com/Author: Listen to the Wind blows the Rain)