SQL Server database user permission settings

SQL Server database user permission settings

In SQL Server 2005 databases, we can set permissions for each user to improve database security. This type of user permission settings are widely used. For example, you can set a user's permission to access a table, or even CRUD, and access permissions to several fields at a lower granularity. In fact, this setting process is very simple. Let's learn it together.

1. Procedure

1. First go to database-level [security]-[Login Name]-[New login name]

(Figure 1: New login name)

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

(Figure 2: Set options)

3. On the "user ing" tab, select the database to be set, set the "architecture", and click "OK" to create a user.

(Figure 3: select the database)

4. Now we can set permissions for the User table in the TestLog database. [Table]-[attribute]

(Figure 4: select a table)

5. On the permissions tab, as shown in, click Add> browse> select object]

(Figure 5: Set the table access user)

6. after clicking confirm, we can find the corresponding permissions in the list below. If you want to refine the column permissions, there is also a "column Permissions" button in the lower right corner for setting. Click "OK" to complete the setting of these permissions.

(Figure 6: Permission List)

7. Now I use the TestUser user to log on to the database, as shown in figure. Now I can only see one table.

(Figure 7: effect)

Ii. Notes

1. In Step 3rd above, note that if the corresponding database is not selected, TestUser cannot be found in the TestLog database.

(Figure 8: TestUser user not found)

2. in step 3rd above, after setting the TestLog data, you need to click OK to complete the User Creation operation. If you set the security object at this time ], you cannot find the newly created TestUser in "add"-"specific object"-"Object Type"-"Login Name"-"Browse.

3. in fact, users created at the database-level [security] belong to the global structure. When a database, such as TestLog, is set, this user will appear in the [Security] list of this database. If you delete the user TestLog, the following message is displayed. After deletion, the user cannot log on. The user needs to be deleted from the corresponding database. If the user is not deleted and created, an error is returned.

(Figure 9: Delete the TestUser user)

4. in the explicit permission list in step 1, if the Control option is selected, it makes no sense to set the column permission in Select, the query will not be restricted. If you set column permission, the error message is displayed normally:

(Figure 10: effect)

5. In the TestLog database, choose security> TestUser> Properties> Security Object> Add> object type.