SQL-DBA-SCRIPTS/030.pr_SetCmdShell.sql at master · spendem/SQL-DBA-SCRIPTS · GitHub

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
USE [DBAdmin]
GO
IF dbo.fn_SQLVersion() >= 9
    BEGIN
        IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[lp_SetCMDShell]') AND type = N'P')
            BEGIN
                PRINT 'Dropping procedure [lp_SetCMDShell] - SQL 2005'
                DROP PROCEDURE [dbo].[lp_SetCMDShell]
            END
    END
ELSE
    BEGIN
        IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'lp_SetCMDShell' AND type = N'P')
            BEGIN
                PRINT 'Dropping procedure [lp_SetCMDShell] - SQL 2000'
                DROP PROCEDURE [dbo].[lp_SetCMDShell]
            END
    END

IF dbo.fn_SQLVersion() >= 9
    BEGIN
        IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr_SetCMDShell]') AND type = N'P')
            BEGIN
                PRINT 'Dropping procedure [pr_SetCMDShell] - SQL 2005'
                DROP PROCEDURE [dbo].[pr_SetCMDShell]
            END
    END
ELSE
    BEGIN
        IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'pr_SetCMDShell' AND type = N'P')
            BEGIN
                PRINT 'Dropping procedure [pr_SetCMDShell] - SQL 2000'
                DROP PROCEDURE [dbo].[pr_SetCMDShell]
            END
    END

GO
IF dbo.fn_SQLVersion() >= 9
    BEGIN
        EXEC dbo.sp_executesql @Statement = N'
            CREATE PROCEDURE [dbo].[pr_SetCMDShell]
                @setval bit,
                @curval bit = NULL OUTPUT
            AS

            DECLARE
                @adv_options bit,
                @xp_cmdshell bit

            IF (IS_SRVROLEMEMBER(''sysadmin'') = 0)
            BEGIN
                RAISERROR (''Only system administrators may run this procedure'', 16, 1)
                RETURN 1
            END

            SELECT @adv_options = CAST(value_in_use AS bit)
            FROM master.sys.configurations
            WHERE name = ''show advanced options''

            SELECT @xp_cmdshell = CAST(value_in_use AS bit)
            FROM master.sys.configurations
            WHERE name = ''xp_cmdshell''

            SET @curval = @xp_cmdshell

            IF (@xp_cmdshell != @setval)
            BEGIN

                IF (@adv_options = 0)
                BEGIN
                    EXEC sp_configure ''show advanced options'', 1
                    RECONFIGURE
                END

                EXEC sp_configure ''xp_cmdshell'', @setval
                RECONFIGURE

                IF (@adv_options = 0)
                BEGIN
                    EXEC sp_configure ''show advanced options'', 0
                    RECONFIGURE
                END

            END

            RETURN'
    END

ELSE
    BEGIN
        EXEC dbo.sp_executesql @Statement = N'
            CREATE PROCEDURE [dbo].[pr_SetCMDShell]
                @setval bit,
                @curval bit = NULL OUTPUT
            AS

            IF (IS_SRVROLEMEMBER(''sysadmin'') = 0)
            BEGIN
                RAISERROR (''Only system administrators may run this procedure'', 16, 1)
                RETURN 1
            END

            SET @curval = 1

            RETURN'
    END
GO

IF dbo.fn_SQLVersion() >= 9
    BEGIN
        IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr_SetCMDShell]') AND type = N'P')
            BEGIN
                PRINT 'Procedure Created [pr_SetCMDShell] - SQL 2005'
            END
    END
ELSE
    BEGIN
        IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'pr_SetCMDShell' AND type = N'P')
            BEGIN
                PRINT 'Procedure Created [pr_SetCMDShell] - SQL 2000'
            END
    END

GO