问题介绍:
很多时候,我们会在SQL Server中新建一个数据库,使用 enterprise manager 的图形界面来完成新建角色、新建登录 用户、分配角色权限,将登录用户加附到角色等操作。这些操作很是烦琐,没有人想做第二次。但 SQL Server 的迁移虽然是很简单,但也有不方便的地方。数据库从备份中新建时,登录用户密码和授权都不能很好的转过来(出于安全考虑),而导出的脚本中也不会有密码项。
实际情况:
公司有一个项目,原来设计时使用了大量的登录用户和角色权限。多次迁移时都出现了失误,这一次要重建一个测试环境时失误又出现了。人不能在同一地方跌倒两次,于是补充了一个脚本来完成这件事。 数据库维护这一块自已不是多熟悉,自己尝试之处就不多说了,直接列出过程。如果哪位朋友觉得不符合数据库的维护惯例, 还请指出。
过程步骤:
1. 假设数据库已生成,表和存储过程等等对象都已建好
2. 在原型数据库中生成相关SQL脚本
2.1 在生成SQL脚本对话框中,生成"角色及角色成员添加.sql"
a. 在选项Tab中:编写数据库用户和数据库角色脚本
脚本例:
if not exists (select * from dbo.sysusers where name = N'r_trade' and uid > 16399)
EXEC sp_addrole N'r_trade'
GO
exec sp_addrolemember N'r_trade', N'e_pos_signin'
GO
2.2 在生成SQL脚本对话框中,生成"角色权限添加.sql"
a. 在常规Tab中:选择"全部表"、"全部视图"、"全部存储过程项"
b. 在设置格式Tab中: 去掉所有选项
c. 在选项Tab中:选择"编写对象权限级别的脚本"
脚本例:
GRANT EXECUTE ON [dbo].[p_sign_in] TO [r_trade]
GO
3. 自己编写一个生成登录用户的脚本
3.1 编写脚本 "登录用户.sql"
脚本例:
EXEC p_create_login N'e_test, N'e_pos_signin', N'e_pos_signin_pwd', N''
GO
3.2 脚本将要调用的存储过程
创建代码:
@login_db nvarchar(128), /*数据库名称*/
@login_name nvarchar(128), /*登录名称*/
@login_pwd nvarchar(128), /*登录密码*/
@login_role nvarchar(128) /*所属角色,此参数在此未用上*/
AS
if exists (select * from dbo.sysusers where name = @login_name)
EXEC sp_revokedbaccess @login_name
if exists (select * from master..sysxlogins where name = @login_name)
EXEC sp_droplogin @login_name
if not exists (select * from master..sysxlogins where name = @login_name)
EXEC sp_addlogin @login_name, @login_pwd, @login_db
EXEC sp_grantdbaccess @login_name, @login_name
if len(@login_role) > 0
EXEC sp_addrolemember @login_attr, @login_name
4.建立批处理 patch.bat,减少操作失误
osql -Stest -de_test -Usa -P12345 -i登录用户.sql
osql -Stest -de_test -Usa -P12345 -i角色及角色成员添加.sql
osql -Stest -de_test -Usa -P12345 -i角色权限添加.sql
参数说明
-S 数据服务名。我用的数据库端口被修正过,这里用的名字是 SQL Server Client Network Utility中的 Server Alias
-d 数据库名。
-U 用户名。用sa超级用户
-P 密码。
-i 要执行的脚本。
5. 最后要做的就是 RUN, 祝大家好运