sql server 高级指令开启创建管理员等模板

-- 检查 'remote debug' 选项是否存在
USE master;
SELECT * FROM sys.configurations
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE name = 'remote debugging')
BEGIN
    print('The configuration option ''remote debugging'' does not exist.')
END
--GRANT DEBUG CONNECT SQL TO sa;
-- 开启 SQL Server 远程调试功能
RECONFIGURE
EXEC sp_configure 'remote admin connections', 1;
GO
EXEC sp_configure 'remote access', 1;
GO
-- 启用高级选项
EXEC sp_configure 'show advanced options', 1;
--EXEC sp_configure 'remote debug enabled', 1;

-- 更新“clr enabled”配置选项，因为CLR必须启用才能进行远程调试
EXEC sp_configure 'clr enabled', 1;


USE master
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ole Automation Procedures', 1;--解决一个错误
RECONFIGURE;



-- 更改“max degree of parallelism”以确保不会有意外的并行查询导致远程调试中断
EXEC sp_configure 'max degree of parallelism', 1;
GO

-- 重置进程缓存，以确保更改立即生效
DBCC FREEPROCCACHE;
GO



DECLARE @DebuggingUserName VARCHAR(50) = 'test4'
DECLARE @WindowsUser VARCHAR(50) = 'PC-L\Administrator'
DECLARE @sql NVARCHAR(MAX);
SELECT * FROM sys.server_principals  where name in ('sa',@WindowsUser)
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @WindowsUser)
BEGIN
    --创建 SQL 登录用户 u234，并授予远程访问调试存储过程的权限：
    PRINT('Window登录用户 '+@WindowsUser+'不存在')
    SET @sql = 'CREATE LOGIN ['+@WindowsUser+'] FROM WINDOWS;GRANT EXECUTE ON master.sys.xp_msver TO ['+@WindowsUser+'];';


    print('sql:'+@sql);
    EXECUTE sp_executesql @sql;
END
ELSE
BEGIN
 PRINT('Window登录用户  '+@WindowsUser+'存在')
END

EXEC sp_addrolemember 'db_datareader', @WindowsUser
EXEC sp_addrolemember 'db_datawriter', @WindowsUser

EXEC sp_addsrvrolemember @loginame = @WindowsUser, @rolename = 'sysadmin';

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = @DebuggingUserName)
BEGIN
    SET @sql = 'CREATE LOGIN '+@DebuggingUserName+' WITH PASSWORD =''123456''';
    EXECUTE sp_executesql @sql;
    SET @sql = 'CREATE USER '+@DebuggingUserName+' FOR LOGIN '+@DebuggingUserName;
    EXECUTE sp_executesql @sql;
    print('-------------创建用户完毕')
END
ELSE
BEGIN
print('用户'+@DebuggingUserName+'无需创建了')
END
EXEC sp_addrolemember 'db_datareader', @DebuggingUserName
EXEC sp_addrolemember 'db_datawriter', @DebuggingUserName
EXEC sp_addsrvrolemember @loginame = @DebuggingUserName, @rolename = 'sysadmin';

SET @sql = 'GRANT CONNECT SQL TO '+@DebuggingUserName+';'
EXECUTE sp_executesql @sql;
print('授权 connect sql 完毕')



SET @sql = 'GRANT EXECUTE ON master.sys.xp_msver TO '+@DebuggingUserName+';';
EXECUTE sp_executesql @sql;
--查询存储过程定义
SELECT OBJECT_DEFINITION(OBJECT_ID('[UDP_PDA_CHKUPREEL]')) AS [Stored Procedure Definition];
return;




GRANT EXECUTE ON xp_cmdshell TO [TEST1]

EXEC sp_addrolemember 'db_datareader', @DebuggingUserName
EXEC sp_addrolemember 'db_datawriter', @DebuggingUserName

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = @WindowsUser)
BEGIN
    CREATE USER [@WindowsUserName] FOR LOGIN [@WindowsUser]
END


EXEC sp_addrolemember 'db_datareader', @WindowsUser
EXEC sp_addrolemember 'db_datawriter', @WindowsUser


IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'your_stored_procedure')
BEGIN
    RAISERROR('Stored procedure your_stored_procedure not found.', 16, 1)
END

SELECT * FROM sys.database_permissions


IF NOT EXISTS (SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID(@DebuggingUserName) AND major_id = OBJECT_ID('MyDB') AND type = 'EX')
BEGIN
    GRANT EXECUTE ON MyDB TO [@DebuggingUserName]
END