SQL Server数据库有登录名、用户、架构、角色等概念,在此不重复解释。
其中架构名,原则上是不允许修改的(重命名),但当特殊业务场景需要时,可以通过创建新架构名,然后批量修改对象的架构来实现。
1、先创建一个新的架构(登录名、用户、架构);2、将原架构下的数据库对象批量改到新架构下;3、删除原有架构
-- 创建服务器的login[登录名],设置密码、检查策略,同时设置默认数据库 CREATE LOGIN LC039999 WITH PASSWORD='aaaaaa', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE = [Northwind]; GO -- 给指定login[登录名]设置服务器角色 ALTER SERVER ROLE [dbcreator] ADD MEMBER [LC039999]; GO USE [Northwind]; -- 在特定数据库下创建用户,绑定[登录名],同时设置默认[schema] CREATE USER LC039999 FROM LOGIN LC039999 WITH DEFAULT_SCHEMA=LC039999; GO -- 给数据库用户设置数据库角色 ALTER ROLE [db_owner] ADD MEMBER [LC029999]; GO -- 创建数据库[schema],设置schema所有者为指定数据库用户 CREATE SCHEMA LC039999 AUTHORIZATION LC039999; GO
在业务库执行如下SQL,可以创建批量修改schema的存储过程。
创建完成后,执行该存储过程即可批量修改。
建议:执行前做好数据库备份。
/* 执行存储过程的demo EXEC dbo.upx_changeSchema 'dbo', -- 旧schema名称 'lc029999'; -- 新schema名称 */ IF OBJECT_ID('dbo.upx_changeSchema') IS NOT NULL BEGIN DROP PROC dbo.upx_changeSchema; END; GO CREATE PROC dbo.upx_changeSchema(@old_schema VARCHAR(200), @new_schema VARCHAR(200)) AS BEGIN --declare @old_schema varchar(200) = 'dbo'; --declare @new_schema varchar(200) = 'dbo'; DECLARE @objName VARCHAR(200); DECLARE csr CURSOR FOR SELECT NAME FROM sys.objects WHERE TYPE IN ('U', 'V', 'FN', 'P') AND SCHEMA_NAME([SCHEMA_ID]) = @old_schema AND [OBJECT_ID] != OBJECT_ID('dbo.upx_changeSchema') OPEN csr FETCH NEXT FROM csr INTO @objName WHILE (@@FETCH_STATUS=0) BEGIN --PRINT 'ALTER SCHEMA ' + @new_schema + ' TRANSFER ' + @old_schema + '.' + @objName exec('ALTER SCHEMA ' + @new_schema + ' TRANSFER ' + @old_schema + '.' + @objName); FETCH NEXT FROM csr INTO @objName END CLOSE csr DEALLOCATE csr END;
最后,根据业务需要决定是否删除原有的架构、用户及登录名。
注意:有严格的顺序要求。
DROP SCHEMA LC029999; DROP USER LC029999; DROP LOGIN LC029999;