** Catalog ：
1. The experiment purpose
2. Experimental content
3. Question answering
**
sql Code
https://download.csdn.net/download/weixin_50836014/85970089

1. The experiment purpose

Through this experiment , master SQL Server Security mechanism , Include SQL Server Security 、 Database users and permissions , Master authentication 、 Sign in 、 Database users 、 Server role 、 Database role 、 Related concepts such as permission and authorization method and their relationship .

2. Experimental content

（1） Create a database . Create a student basic information table under the newly created database , Insert several records .

create database test1
use test1
create table stu(
		 full name  varchar(20),
		 Student number  varchar(10),
		 Gender  varchar(5))


insert into stu (  full name , Student number , Gender )
values	(' Zhang San ','1110191111',' male '),
		(' Li Si ','1110191259',' Woman '),
		(' Wang Wu ','1110191389',' male '),
		(' Sun Liu ','1110192596',' Woman ');

（2） Create a Windows Authenticated login 1, Set the default database to Master database . Will log in 1 Set as database system administrator .

-- Sign in 1 to grant authorization 
USE [master]
GO
CREATE LOGIN [DESKTOP-IL88V2D\test1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DESKTOP-IL88V2D\test1]
GO

View login 1 Configuration of ：

（3） Create a Windows Authenticated login 2, Set the default database to the newly created database . Authorized login 2 For the administrator of the newly created database .

-- Sign in 2 to grant authorization 
USE [test1]
GO
CREATE LOGIN [DESKTOP-IL88V2D\test2] FROM WINDOWS WITH DEFAULT_DATABASE=[test1]
GO
ALTER USER [DESKTOP-IL88V2D\test2] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [DESKTOP-IL88V2D\test2]
GO

Check login 2 Authority ：

（4） Undo login 2 Administrator rights of the newly created database .

-- Sign in 2 Right to receive 
GO
ALTER ROLE [db_owner] Drop MEMBER [DESKTOP-IL88V2D\test2]
GO

（5） Authorized login 2 The authority to query and update some fields in the student basic information table . Verify query 、 Have permission to update 、 Update without permission 、 No permission to insert and delete .

-- Sign in 2 Re authorization of 
use [test1]
GO
GRANT SELECT ON stu TO [DESKTOP-IL88V2D\test2]
GO
GRANT UPDATE ( Student number ) ON stu TO [DESKTOP-IL88V2D\test2]
GO

Sign in 2 Validation of the ：

Yes select jurisdiction ：

Have update permission ：

No update permission ：

Not authorized to insert ：

You do not have permission to delete ：

3. Answer the following questions

（1） Briefly describe what is the default database and explain the difference between different default databases after logging in to the database server with the experimental results .

 The default database is the database that can be used by default after logging in with the user name , Users have certain permissions to operate the default database . Due to different user permissions , Operational databases are also different , Such as login above 2, It can only operate on the default database , No permission to operate other databases .

（2） Explain the content of the experiment （2）、（3）、（5） Differences in authorization methods used in , Briefly describe the advantages and disadvantages of each method .

（2） The authorization in is to log in 1 As the administrator of the entire database server , Sign in 1 You can get all permissions of all databases , There are security risks .
（3） Authorization in enables login 2 Obtained all the operation permissions of a specific database , But you cannot operate on other databases , safer .
（5） Authorization in only grants login 2 Some operation permissions in a database , Safety is better than （2） and （3）.

4.SQL Code

https://download.csdn.net/download/weixin_50836014/85970089