SQL Server 2005 Control User Rights Access table

Transferred from: http://www.cnblogs.com/gaizai/archive/2011/07/14/2106617.html

First, Demand

In the management of the database process, we often need to control the access of a user to the database, such as only need to give the user access to a table permissions, or even crud permissions, the smaller the granularity can go to a few fields access rights. Writing this article is a description of the operation process.

In fact, this is just a simple SQL Server permissions management, some places do not understand and tell, just hope that some of the newly-introduced children's shoes help, other heroes are: I classroom startled, and then have to laugh. (Holy grail of gambling)

Second, Operation Steps

1. First Enter database-level security-Login name-New login

( Figure 1: New login name )

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

( Figure 2: Setting options )

3. in the User Mappings tab, as shown in, tick the database you need to set up, and set the "Schema", click "Confirm" button to complete the creation of the user's 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. in the "Permissions" tab, as shown, click "Add"-"Browse"-"Select Object"

( Figure 5: Setting the user to access the table )

6. after clicking on "Confirm" above, we can find the corresponding permission in the list below, if you want to refine the permissions of the column, there is a "column permission" button in the lower right corner to set, click "Confirm" button to complete the settings of these permissions

( Figure 6: Permission list )

7. now use the testuser user login database, after landing as shown, now only see a table

( Figure 7: Effects )

Third, Precautions

1. in the 3rd step above, it is important to note that if the corresponding database is not selected, then the testuser will not be found in the Testlog database.

( Figure 8: TestUser user not found )

< Span style= "font-family: ' Microsoft Jas Black ', ' sans-serif ';" >2.       

3. In fact, the user created at the database level "security" is a global one, and when a database is set up, such as Testlog, the user appears in the security list of the database. If you delete testlog This user, you will receive the following prompt. Once deleted, the user will not be able to log in. Need to go to the corresponding database to delete the user, if not deleted and created, will be an error.

( Figure 9: Delete testuser user )

4. In the "explicit Permissions" list in step 6th, if you select the "Control" option, setting the Query "column permissions" in the "select" is meaningless and the query is not restricted. If you set "column Permissions", the error message will be displayed under normal circumstances:

( Figure 10: Effects )

5. in the Testlog database "security"-"TestUser"-"Properties"-"securable"-"Add"-"Object type" Here are more about the database level of some object types can be set.