简书链接:sqlserver高级指令开启创建管理员等模板
文章字数：219,阅读全文大约需要1分钟

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
-- 检查 '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