-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathCreateMcpLogin.sql
More file actions
349 lines (306 loc) · 14.3 KB
/
CreateMcpLogin.sql
File metadata and controls
349 lines (306 loc) · 14.3 KB
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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
/*
Script: CreateMcpLogin.sql
Description: Creates a least-privilege SQL Server login and database users for
AI-driven DBA monitoring via an MCP (Model Context Protocol) server.
Grants VIEW SERVER STATE and VIEW ANY DEFINITION at server level,
db_datareader + VIEW DATABASE STATE across configurable databases,
and conditional EXECUTE grants on popular community diagnostic tools
(sp_WhoIsActive, First Responder Kit sp_Blitz* / sp_ineachdb,
Erik Darling's sp_pressuredetector / sp_QuickieStore /
sp_LogHunter) where they exist.
Prerequisites: - SQL Server 2016 SP1+ (uses CREATE LOGIN, ALTER ROLE ... ADD MEMBER)
- Executed in the context of the master database
- Requires sysadmin or securityadmin to create logins and grant
server-level permissions, plus db_owner in each target database
to create users and grant database-level permissions
Configuration: @LoginName - Name for the SQL login (default: claude_dba)
@Password - Strong password for the login (MUST be changed)
@DatabaseList - Comma-separated list of databases to grant access to
Usage Notes: - Review the password before running; never use the placeholder value
in production. Use a cryptographically random password of at least
40 characters.
- The login is created with CHECK_POLICY = ON and CHECK_EXPIRATION = OFF
by default. Adjust to match your security policy.
- The script is idempotent and safe to re-run. Existing logins and
users are detected and skipped; permissions are re-applied.
- Community tool EXECUTE grants are conditional: they are only applied
when the procedure exists in the target database.
- No data-modification permissions are granted (no db_datawriter,
no DDL rights). This login is strictly read-only plus diagnostic
procedure execution.
- The msdb database is always configured (required for backup history
and SQL Agent job monitoring) regardless of the @DatabaseList value.
License: MIT License - https://opensource.org/licenses/MIT
Source: https://github.com/mbentham/sql-server-scripts
*/
USE master;
GO
-- ========================================
-- CONFIGURATION SECTION
-- Update these values for your environment
-- ========================================
DECLARE @LoginName NVARCHAR(128) = N'claude_dba'; -- Name of the SQL login to create
DECLARE @Password NVARCHAR(128) = N'<YourStrongPasswordHere>'; -- CHANGE THIS: use a strong, unique password
DECLARE @DatabaseList NVARCHAR(MAX) = N'master'; -- Comma-separated list of databases to grant access to
-- e.g. N'master,DBA,YourAppDb,AnotherDb'
-- ========================================
-- END OF CONFIGURATION
-- ========================================
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @DatabaseName NVARCHAR(128);
DECLARE @Pos INT;
DECLARE @ErrorMessage NVARCHAR(4000);
-- ========================================
-- Validate configuration
-- ========================================
IF @Password = N'<YourStrongPasswordHere>' OR @Password = N'' OR @Password IS NULL
BEGIN
RAISERROR('ERROR: You must set @Password to a strong, unique value before running this script.', 16, 1);
RETURN;
END
-- ========================================
-- Create the login (server level)
-- ========================================
BEGIN TRY
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @LoginName)
BEGIN
SET @SQL = N'CREATE LOGIN ' + QUOTENAME(@LoginName)
+ N' WITH PASSWORD = ' + QUOTENAME(@Password, '''')
+ N', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;';
EXEC sp_executesql @SQL;
PRINT 'Login ' + @LoginName + ' created successfully.';
END
ELSE
PRINT 'Login ' + @LoginName + ' already exists.';
-- Grant server-level permissions for monitoring DMVs and metadata
SET @SQL = N'GRANT VIEW SERVER STATE TO ' + QUOTENAME(@LoginName) + N';';
EXEC sp_executesql @SQL;
SET @SQL = N'GRANT VIEW ANY DEFINITION TO ' + QUOTENAME(@LoginName) + N';';
EXEC sp_executesql @SQL;
PRINT 'Server-level permissions granted (VIEW SERVER STATE, VIEW ANY DEFINITION).';
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE();
RAISERROR('ERROR creating login or granting server permissions: %s', 16, 1, @ErrorMessage);
RETURN;
END CATCH
-- ========================================
-- Grant EXECUTE on xp_readerrorlog in master
-- (Required by sp_LogHunter; on SQL Server 2019 and earlier,
-- VIEW SERVER STATE alone is not sufficient)
-- ========================================
BEGIN TRY
SET @SQL = N'
USE [master];
DECLARE @GrantSQL NVARCHAR(500) = N''GRANT EXECUTE ON [dbo].[xp_readerrorlog] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @GrantSQL;
PRINT ''EXECUTE granted on master.dbo.xp_readerrorlog.'';
';
EXEC sp_executesql @SQL, N'@Login NVARCHAR(128)', @Login = @LoginName;
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE();
PRINT 'WARNING: Could not grant EXECUTE on xp_readerrorlog: ' + @ErrorMessage;
PRINT ' sp_LogHunter may not work on SQL Server 2019 and earlier.';
END CATCH
-- ========================================
-- Setup permissions in msdb database
-- (Required for backup history and SQL Agent job monitoring)
-- ========================================
BEGIN TRY
SET @SQL = N'
USE [msdb];
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @Login)
BEGIN
DECLARE @CreateSQL NVARCHAR(500) = N''CREATE USER '' + QUOTENAME(@Login) + N'' FOR LOGIN '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @CreateSQL;
PRINT ''User '' + @Login + '' created in msdb.'';
END
ELSE
PRINT ''User '' + @Login + '' already exists in msdb.'';
DECLARE @RoleSQL NVARCHAR(500) = N''ALTER ROLE [db_datareader] ADD MEMBER '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @RoleSQL;
DECLARE @GrantSQL NVARCHAR(500) = N''GRANT EXECUTE ON [dbo].[agent_datetime] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @GrantSQL;
PRINT ''msdb permissions granted (db_datareader, EXECUTE on agent_datetime).'';
';
EXEC sp_executesql @SQL, N'@Login NVARCHAR(128)', @Login = @LoginName;
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE();
RAISERROR('ERROR setting up msdb permissions: %s', 16, 1, @ErrorMessage);
RETURN;
END CATCH
-- ========================================
-- Setup permissions in each target database
-- ========================================
WHILE LEN(@DatabaseList) > 0
BEGIN
-- Parse the next database name from the comma-separated list
SET @Pos = CHARINDEX(',', @DatabaseList);
IF @Pos = 0
BEGIN
SET @DatabaseName = LTRIM(RTRIM(@DatabaseList));
SET @DatabaseList = N'';
END
ELSE
BEGIN
SET @DatabaseName = LTRIM(RTRIM(LEFT(@DatabaseList, @Pos - 1)));
SET @DatabaseList = SUBSTRING(@DatabaseList, @Pos + 1, LEN(@DatabaseList));
END
-- Skip empty entries (e.g. trailing commas)
IF @DatabaseName = N''
CONTINUE;
-- Skip if the database does not exist on this instance
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @DatabaseName)
BEGIN
PRINT 'WARNING: Database [' + @DatabaseName + '] does not exist on this instance. Skipping.';
CONTINUE;
END
PRINT '========================================';
PRINT 'Setting up permissions for database: [' + @DatabaseName + ']';
PRINT '========================================';
-- Create user and assign roles in the target database
BEGIN TRY
SET @SQL = N'
USE [' + QUOTENAME(@DatabaseName, '') + N'];
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @Login)
BEGIN
DECLARE @CreateSQL NVARCHAR(500) = N''CREATE USER '' + QUOTENAME(@Login) + N'' FOR LOGIN '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @CreateSQL;
PRINT ''User '' + @Login + '' created in [' + @DatabaseName + N'].'';
END
ELSE
PRINT ''User '' + @Login + '' already exists in [' + @DatabaseName + N'].'';
-- Read-only access
DECLARE @RoleSQL NVARCHAR(500) = N''ALTER ROLE [db_datareader] ADD MEMBER '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @RoleSQL;
-- DMV access within this database
DECLARE @StateSQL NVARCHAR(500) = N''GRANT VIEW DATABASE STATE TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @StateSQL;
PRINT ''Database roles and permissions assigned in [' + @DatabaseName + N'].'';
';
EXEC sp_executesql @SQL, N'@Login NVARCHAR(128)', @Login = @LoginName;
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE();
PRINT 'ERROR setting up database permissions in [' + @DatabaseName + ']: ' + @ErrorMessage;
-- Continue to the next database rather than aborting entirely
CONTINUE;
END CATCH
-- Grant EXECUTE on community diagnostic procedures (only if they exist)
BEGIN TRY
SET @SQL = N'
USE [' + QUOTENAME(@DatabaseName, '') + N'];
DECLARE @ProcSQL NVARCHAR(500);
-- sp_WhoIsActive (Adam Machanic)
IF OBJECT_ID(N''dbo.sp_WhoIsActive'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_WhoIsActive] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
-- Erik Darling tools
IF OBJECT_ID(N''dbo.sp_pressuredetector'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_pressuredetector] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_PerfCheck'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_PerfCheck] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_LogHunter'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_LogHunter] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_QuickieStore'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_QuickieStore] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
-- First Responder Kit (Brent Ozar)
IF OBJECT_ID(N''dbo.sp_ineachdb'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_ineachdb] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_Blitz'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_Blitz] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_BlitzFirst'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_BlitzFirst] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_BlitzCache'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_BlitzCache] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_BlitzIndex'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_BlitzIndex] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_BlitzLock'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_BlitzLock] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_BlitzQueryStore'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_BlitzQueryStore] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_BlitzWho'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_BlitzWho] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
IF OBJECT_ID(N''dbo.sp_BlitzBackups'', N''P'') IS NOT NULL
BEGIN
SET @ProcSQL = N''GRANT EXECUTE ON [dbo].[sp_BlitzBackups] TO '' + QUOTENAME(@Login) + N'';'';
EXEC sp_executesql @ProcSQL;
END
PRINT ''EXECUTE permissions granted on community diagnostic procedures in [' + @DatabaseName + N'].'';
';
EXEC sp_executesql @SQL, N'@Login NVARCHAR(128)', @Login = @LoginName;
END TRY
BEGIN CATCH
SET @ErrorMessage = ERROR_MESSAGE();
PRINT 'ERROR granting EXECUTE on procedures in [' + @DatabaseName + ']: ' + @ErrorMessage;
END CATCH
END
-- ========================================
-- Summary
-- ========================================
PRINT '';
PRINT '========================================';
PRINT 'Setup complete for login: ' + @LoginName;
PRINT '========================================';
PRINT '';
PRINT 'Server-level permissions:';
PRINT ' - VIEW SERVER STATE';
PRINT ' - VIEW ANY DEFINITION';
PRINT '';
PRINT 'Per-database permissions:';
PRINT ' - db_datareader role membership';
PRINT ' - VIEW DATABASE STATE';
PRINT ' - EXECUTE on community diagnostic procedures (where installed)';
PRINT '';
PRINT 'master permissions:';
PRINT ' - EXECUTE on dbo.xp_readerrorlog';
PRINT '';
PRINT 'msdb permissions:';
PRINT ' - db_datareader role membership';
PRINT ' - EXECUTE on dbo.agent_datetime';
PRINT '';
PRINT 'Example MCP connection string:';
PRINT ' Server=YourServer;Database=YourDatabase;User Id=' + @LoginName + ';Password=<YourPassword>;TrustServerCertificate=True;';
PRINT '========================================';
GO