T-SQL语句循环批量添加登录用户和数据库用户并自动创建架构，为每个用户添加Create table权限...

在大二的时候学了那么久的SQL server数据库，都还没真正了解到数据库架构（schema）这回事，还要谢谢许老师呢，多亏了今天才知道原来SQL server 的登录用户、数据库用户、角色、权限、架构（schema）用起来管理数据也是非常方便的呢！按我自己的理解，schema就是把一个数据库再分成几份，每一份都有一个特定的用户来管理自己的数据库对象（例如数据表、视图等）。这样看来是不是许多互联网网站的的一些个人空间的数据都是在服务器上的数据库然后再分出一个个的架构来对应一个个的用户呢？这个有待了解.......

今天的成果：

1、题目：

账号 sw1 sw2... sw55 密码同账号
每个账号不同schema（架构）
sp_addlogin 登陆名 针对sqlserver 服务器
sp_adduser() 针对数据库 自动创建schema

账号 xx1 xx2... sw32 密码同账号
确定 每个用户 只能访问自己schema create table (ddl) role

循环....

2、解答：

--***********************************************************--
--创建软件111班的数据库登录账号
--***********************************************************--
use Test
go
exec sp_addrole 'sw111' --创建一个班级角色，方便为学生用户分配Create table的权限
go
grant create table to sw111 --为班级角色分配Create table的权限
go
DECLARE @i int
DECLARE @loginname varchar(10) --声明登录名
DECLARE @loginpwd varchar(10) --声明登录密码
DECLARE @username varchar(10) --声明数据库用户
Set @i = 1 --初始的值，可以根据实际修改
--开始循环插入数据；
WHILE @i <= 55 --定义需要添加的记录数
BEGIN
SET @loginname = RIGHT('sw' + CAST(@i AS varchar(5)),5) --获取登录名
set @loginpwd = @loginname --获取登录密码
SET @username = RIGHT('sw' + CAST(@i AS varchar(5)),5) --获取数据库用户
Set @i =@i +1
exec sp_addlogin @loginname, @loginpwd --添加登录用户
exec sp_grantdbaccess @loginname,@username --添加数据库用户
exec sp_addrolemember N'sw111', @username --授予学生用户对自己数据库的权限
END

use Test
go
exec sp_addrole 'xx111' --创建一个班级角色，方便为学生用户分配Create table的权限
go
grant create table to xx111 --为班级角色分配Create table的权限
go
DECLARE @i int
DECLARE @loginname varchar(10) --声明登录名
DECLARE @loginpwd varchar(10) --声明登录密码
DECLARE @username varchar(10) --声明数据库用户
Set @i = 1 --初始的值，可以根据实际修改
--开始循环插入数据；
WHILE @i <= 32 --定义需要添加的记录数
BEGIN
SET @loginname = RIGHT('xx' + CAST(@i AS varchar(5)),5) --获取登录名
set @loginpwd = @loginname --获取登录密码
SET @username = RIGHT('xx' + CAST(@i AS varchar(5)),5) --获取数据库用户
Set @i =@i +1
exec sp_addlogin @loginname, @loginpwd --添加登录用户
exec sp_grantdbaccess @loginname,@username --添加数据库用户
exec sp_addrolemember N'xx111', @username --授予学生用户对自己数据库的权限
END

最后总结：

代码看起来实现很简单，但是自己收获颇丰的！.....继续努力！

转载于:https://www.cnblogs.com/kelvin0916/archive/2012/10/13/2722093.html