一、示例图

二、SQL代码
备注:自行替换声明变量类容即可。
DECLARE @LoginName NVARCHAR(128) = 'user_test';
DECLARE @Password NVARCHAR(128) = '123';
DECLARE @DefaultDB NVARCHAR(128) = 'sqldb';
DECLARE @RoleName NVARCHAR(128) = 'role_test';
DECLARE @ProcedureName NVARCHAR(128) = 'pro_test';
IF NOT EXISTS (SELECT 1 FROM master.sys.server_principals WHERE name = @LoginName)
BEGIN
DECLARE @CreateLoginSQL NVARCHAR(MAX) =
'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = ''' + @Password + ''',
DEFAULT_DATABASE = [' + @DefaultDB + '],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;';
EXEC sp_executesql @CreateLoginSQL;
PRINT '登录账户 ' + @LoginName + ' 创建成功';
END
ELSE
BEGIN
PRINT '登录账户 ' + @LoginName + ' 已存在';
END
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @LoginName AND type = 'S')
BEGIN
DECLARE @CreateUserSQL NVARCHAR(MAX) =
'CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + '];';
EXEC sp_executesql @CreateUserSQL;
PRINT '数据库用户 ' + @LoginName + ' 创建成功';
END
ELSE
BEGIN
PRINT '数据库用户 ' + @LoginName + ' 已存在';
END
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
BEGIN
DECLARE @CreateRoleSQL NVARCHAR(MAX) =
'CREATE ROLE [' + @RoleName + '];';
EXEC sp_executesql @CreateRoleSQL;
PRINT '角色 ' + @RoleName + ' 创建成功';
END
ELSE
BEGIN
PRINT '角色 ' + @RoleName + ' 已存在';
END
DECLARE @GrantProcSQL NVARCHAR(MAX) =
'GRANT EXECUTE ON ' + @ProcedureName + ' TO [' + @RoleName + '];';
EXEC sp_executesql @GrantProcSQL;
PRINT '已授予 ' + @RoleName + ' 执行 ' + @ProcedureName + ' 的权限';
DECLARE @AddRoleMemberSQL NVARCHAR(MAX) =
'EXEC sp_addrolemember ''' + @RoleName + ''', ''' + @LoginName + ''';';
EXEC sp_executesql @AddRoleMemberSQL;
PRINT '已将 ' + @LoginName + ' 添加到 ' + @RoleName + ' 角色';