SQLServer

-- 1.创建数据库
create database TestDB1
go

create database TestDB2
go

-- 2.创建测试表:在数据库TestDB1/TestDB2中创建表TestTable 默认dbo schema
use TestDB1
go
create table TestTable
(
    Num        int primary key,
    Name    nvarchar(20),
    Addr    nvarchar(50)
)
go

use TestDB2
GO
create table TestTable
(
    Num        int primary key,
    Name    nvarchar(20),
    Addr    nvarchar(50)
)
go

-- 3.向表TestTable中插入数据
use TestDB1
insert into TestTable values(101, 'ACCOUNTING', 'NEW YORK');
insert into TestTable values(201, 'RESEARCH', 'DALLAS');
insert into TestTable values(301, 'SALES', 'CHICAGO');
insert into TestTable values(401, 'OPERATIONS', 'BOSTON');
select * from TestTable        -- 查看插入结果

use TestDB2
insert into TestTable values(101, 'ACCOUNTING', 'NEW YORK');
insert into TestTable values(201, 'RESEARCH', 'DALLAS');
insert into TestTable values(301, 'SALES', 'CHICAGO');
insert into TestTable values(401, 'OPERATIONS', 'BOSTON');
select * from TestTable        -- 查看插入结果

-- 4.查看数据库sechema, user的存储结果
use TestDB1
select * from sys.database_principals
select * from sys.schemas
select * from sys.server_principals

-- 5.创建登陆账户: DuanLaoYe 密码123456 
create login DuanLaoYe with password = '123456', default_database = TestDB1

-- 6.为登陆账户创建数据库用户
use TestDB2
create user DLY for login DuanLaoYe with default_schema = dbo

-- 7.通过假如数据库角色,赋予数据库用户'db_owner'权限
use TestDB1
exec sp_addrolemember 'db_owner', 'DLY'

-- 8.让登陆用户DuanLaoYe访问多个数据库
use TestDB2
go
create user DLY2 for login DuanLaoYe with default_schema = dbo
go
exec sp_addrolemember 'db_owner',DLY2
go

-- 9.禁用登陆账户
alter login DuanLaoYe disable

-- 10.启用登陆账户
alter login DuanLaoYe enable

-- 11.更改登陆账户名称
alter login DuanLaoYe with name = Change_Name_DuanLaoYe

-- 12.更改登陆账户密码
alter login Change_Name_DuanLaoYe with password = '654321'

-- 13.更改数据库用户名称
use TestDB1
alter user DLY with name = Change_Name_DLY1

use TestDB2
alter user DLY with name = Change_Name_DLY2

-- 14.删除数据库用户: Change_Name_DLY1 / Change_Name_DLY2
use TestDB1
drop user Change_Name_DLY1

use TestDB2
drop user Change_Name_DLY2

-- 15.删除登陆账户:Change_Name_DuanLaoYe
drop login Change_Name_DuanLaoYe

-- 16.删除数据库: TestDB1 / TestDB2
drop database TestDB1
go