Files
GX.O3.WMS/WebApi/docs/create_user_GS4229.sql

69 lines
2.7 KiB
Transact-SQL
Executable File
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
/*
* 参照 GS3516 创建用户 GS4229(库:GXWMS @ 10.10.10.101)
*
* 执行方式:SSMS 连上 GXWMS 库后执行,或 sqlcmd:
* sqlcmd -S 10.10.10.101 -d GXWMS -U sa -P 'Gx@20240111' -i create_user_GS4229.sql
*
* 注意:密码(GX_PASSWORD)与角色(SYS_USERROLERELATION)直接复制 GS3516;
* USER_NAME 默认设为 'GS4229',如需真实姓名请修改 @userName。
*/
-- 0. 预览 GS3516(确认后再放开下面的 INSERT)
SELECT * FROM SYS_LOGINUSER WHERE USER_ID = 'GS3516';
SELECT * FROM SYS_USERROLERELATION WHERE USER_ID = 'GS3516';
DECLARE @src NVARCHAR(50) = N'GS3516';
DECLARE @dst NVARCHAR(50) = N'GS4229';
DECLARE @userName NVARCHAR(100) = N'GS4229'; -- 需要真实姓名时改这里
IF EXISTS (SELECT 1 FROM SYS_LOGINUSER WHERE USER_ID = @dst)
BEGIN
RAISERROR(N'目标用户 GS4229 已存在,已终止。', 16, 1);
RETURN;
END
IF NOT EXISTS (SELECT 1 FROM SYS_LOGINUSER WHERE USER_ID = @src)
BEGIN
RAISERROR(N'参照用户 GS3516 不存在,已终止。', 16, 1);
RETURN;
END
DECLARE @cols NVARCHAR(MAX), @sel NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- 1. 复制用户主表(自动排除自增列/计算列;USER_ID 用新值,USER_NAME 用 @userName)
SELECT @cols = STUFF((
SELECT ',' + QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('SYS_LOGINUSER') AND c.is_identity = 0 AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH('')), 1, 1, '');
SET @sel = REPLACE(REPLACE(@cols, '[USER_ID]', '@dst'), '[USER_NAME]', '@userName');
SET @sql = N'INSERT INTO SYS_LOGINUSER (' + @cols + N')
SELECT ' + @sel + N' FROM SYS_LOGINUSER WHERE USER_ID = @src';
PRINT @sql;
EXEC sp_executesql @sql,
N'@src NVARCHAR(50), @dst NVARCHAR(50), @userName NVARCHAR(100)',
@src, @dst, @userName;
-- 2. 复制角色关系(SYS_USERROLERELATION)
IF OBJECT_ID('SYS_USERROLERELATION') IS NOT NULL
BEGIN
SELECT @cols = STUFF((
SELECT ',' + QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('SYS_USERROLERELATION') AND c.is_identity = 0 AND c.is_computed = 0
ORDER BY c.column_id
FOR XML PATH('')), 1, 1, '');
SET @sel = REPLACE(@cols, '[USER_ID]', '@dst');
SET @sql = N'INSERT INTO SYS_USERROLERELATION (' + @cols + N')
SELECT ' + @sel + N' FROM SYS_USERROLERELATION WHERE USER_ID = @src';
PRINT @sql;
EXEC sp_executesql @sql, N'@src NVARCHAR(50), @dst NVARCHAR(50)', @src, @dst;
END
-- 3. 校验
SELECT USER_ID, USER_NAME, GX_PASSWORD FROM SYS_LOGINUSER WHERE USER_ID IN ('GS3516', 'GS4229');
SELECT r.* FROM SYS_USERROLERELATION r WHERE r.USER_ID = 'GS4229';