SQLServer - 登陆账户/数据库用户操作

 

  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

 

posted @ 2016-03-08 15:39  C/C++/Python/Java  阅读(747)  评论(0)    收藏  举报