1 -- 1.创建数据库
2 create database TestDB1
3 go
4
5 create database TestDB2
6 go
7
8 -- 2.创建测试表:在数据库TestDB1/TestDB2中创建表TestTable 默认dbo schema
9 use TestDB1
10 go
11 create table TestTable
12 (
13 Num int primary key,
14 Name nvarchar(20),
15 Addr nvarchar(50)
16 )
17 go
18
19 use TestDB2
20 GO
21 create table TestTable
22 (
23 Num int primary key,
24 Name nvarchar(20),
25 Addr nvarchar(50)
26 )
27 go
28
29 -- 3.向表TestTable中插入数据
30 use TestDB1
31 insert into TestTable values(101, 'ACCOUNTING', 'NEW YORK');
32 insert into TestTable values(201, 'RESEARCH', 'DALLAS');
33 insert into TestTable values(301, 'SALES', 'CHICAGO');
34 insert into TestTable values(401, 'OPERATIONS', 'BOSTON');
35 select * from TestTable -- 查看插入结果
36
37 use TestDB2
38 insert into TestTable values(101, 'ACCOUNTING', 'NEW YORK');
39 insert into TestTable values(201, 'RESEARCH', 'DALLAS');
40 insert into TestTable values(301, 'SALES', 'CHICAGO');
41 insert into TestTable values(401, 'OPERATIONS', 'BOSTON');
42 select * from TestTable -- 查看插入结果
43
44 -- 4.查看数据库sechema, user的存储结果
45 use TestDB1
46 select * from sys.database_principals
47 select * from sys.schemas
48 select * from sys.server_principals
49
50 -- 5.创建登陆账户: DuanLaoYe 密码123456
51 create login DuanLaoYe with password = '123456', default_database = TestDB1
52
53 -- 6.为登陆账户创建数据库用户
54 use TestDB2
55 create user DLY for login DuanLaoYe with default_schema = dbo
56
57 -- 7.通过假如数据库角色,赋予数据库用户'db_owner'权限
58 use TestDB1
59 exec sp_addrolemember 'db_owner', 'DLY'
60
61 -- 8.让登陆用户DuanLaoYe访问多个数据库
62 use TestDB2
63 go
64 create user DLY2 for login DuanLaoYe with default_schema = dbo
65 go
66 exec sp_addrolemember 'db_owner',DLY2
67 go
68
69 -- 9.禁用登陆账户
70 alter login DuanLaoYe disable
71
72 -- 10.启用登陆账户
73 alter login DuanLaoYe enable
74
75 -- 11.更改登陆账户名称
76 alter login DuanLaoYe with name = Change_Name_DuanLaoYe
77
78 -- 12.更改登陆账户密码
79 alter login Change_Name_DuanLaoYe with password = '654321'
80
81 -- 13.更改数据库用户名称
82 use TestDB1
83 alter user DLY with name = Change_Name_DLY1
84
85 use TestDB2
86 alter user DLY with name = Change_Name_DLY2
87
88 -- 14.删除数据库用户: Change_Name_DLY1 / Change_Name_DLY2
89 use TestDB1
90 drop user Change_Name_DLY1
91
92 use TestDB2
93 drop user Change_Name_DLY2
94
95 -- 15.删除登陆账户:Change_Name_DuanLaoYe
96 drop login Change_Name_DuanLaoYe
97
98 -- 16.删除数据库: TestDB1 / TestDB2
99 drop database TestDB1
100 go
