SQL Server logins, database users, database roles and Schemas

 

[Introduction]

 You work as a database administrator for your company. a SQL Server login named Tom is used only withinAdventureworksDatabase.

A new company security policy is being implemented. This new policy states that SQL Server Authentication cannot be used. In preparation for implementation of the policy, you must remove the Tom login.

The new policy states that users associated with SQL Server logins must be removed, and the ownership of anything that is owned by these users must be transferredDBO.

 

 

To complete this task, the following is a recommended procedure.

1.Database-Adventureworks-Security-Schema-Tom (if exists)-properties-general, change the schema owner from Tom to DBO.

2.Database-Adventureworks-Security-users-Tom, delete Tom.

3.Server-security-logins, delete the Tom

 

OKThe task is completed. But we can't just satisfy it. Do you really know why you want to do it in this order, and do you understand the terms involved in each step.

 

[Body]

 

1. SQL Server logins

(1)Create a user who can log on to the database. Enter the login name, password, and default database, but if you are notUser MappingThe database is not specified. You can create the user successfully. However, when the user logs on, a prompt is displayed.4064Error. The solution is naturally obvious.

(2) When you are User Mapping After the corresponding database is specified, SQL Server A user with the same name is automatically created under the database. And the default architecture is DBO . At this time, you have successfully logged on to the database, but when you execute any T-SQL Statement. Because it does not belong to any database role. Even if he has an architecture.

 

2. database users

(1) database users are database-level subjects. Each database user is Public A member of the role.

(2)You can select a specific architecture for the user and set various database objects under the architecture for the user(Tables, views, functions, and stored procedures).

3. Database roles

(1)Two database-level roles:SQL ServerPreset fixed roles: User-defined roles.

(2)You can add a database user to a database role.

(3) You can select an architecture for a role, set the database objects in the architecture for the user ( table, view, function, stored Procedure ) permission. [Same as 2. (2) ]

 

4. database schemas

(1)Architecture refers to containers that contain tables, views, and processes. It is located inside the database, while the database is located inside the server. These entities are placed together like nested boxes. The server is the outermost frame, while the architecture is the innermost frame. The schema contains all the security objects listed below, but it does not contain any other boxes.
(2)You can select user roles and users for the architecture. After you set permissions here, the permissions of users or roles are valid for all objects in the architecture.

 

 

5Exercise.

(1)Create a databaseDb1,Create an architectureHR,

(2) Create three user roles Hr_reader, hr_writer And Hr_admin. hr_reader Only Select Permission , Hr_writer Yes Select And Update Permission, Hr_admin Yes Select, update, delete And Execute Permission.

(3) create three logins HRA, HRR, HRW corresponding database roles hr_reader, hr_writer and hr_admin.

(4) Create a table HR. employee, Field em_id, first_name, last_name

(5)Create a stored procedureSp_listemployees

(6)Test whether the permissions of each user are set correctly.