1
2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */
3 /* 4.8 1.1 06/14/90 sproc/src/diskdefault */
4
5 /*
6 ** Messages for "sp_diskdefault" 17470
7 **
8 ** 17260, "Can't run %1! from within a transaction."
9 ** 17470, "No such device exists -- run sp_helpdevice to list the DataServer devices."
10 ** 17471, "The device name supplied is not a database disk."
11 ** 17472, "Usage: sp_diskdefault logicalname {defaulton | defaultoff}."
12 ** 17473, "The device '%1!' is a private device. This operation is not valid for private devices."
13 */
14
15 create procedure sp_diskdefault
16 @logicalname varchar(30), /* logical name of the device */
17 @defstatus varchar(15) /* turn on or off */
18 as
19
20
21 /*
22 ** If we're in a transaction, disallow this since it might make recovery
23 ** impossible.
24 */
25 if @@trancount > 0
26 begin
27 /*
28 ** 17260, "Can't run %1! from within a transaction."
29 */
30 raiserror 17260, "sp_diskdefault"
31 return (1)
32 end
33 else
34 begin
35 set chained off
36 end
37
38 set transaction isolation level 1
39
40 /* check if user has sa role, proc_role will also do auditing
41 ** if required. proc_role will also print error message if required.
42 */
43
44 if (proc_role("sa_role") = 0)
45 return (1)
46
47 /*
48 ** Make sure that a device with @logicalname exists.
49 */
50 if not exists (select *
51 from master.dbo.sysdevices
52 where name = @logicalname)
53 begin
54 /*
55 ** 17470, "No such device exists -- run sp_helpdevice to list the DataServer devices."
56 */
57 raiserror 17470
58 return (1)
59 end
60
61 /*
62 ** Make sure that is a database disk and not a dump device.
63 */
64 if exists (select *
65 from master.dbo.sysdevices
66 where name = @logicalname
67 and status & 16 = 16)
68 begin
69 /*
70 ** 17471, "The device name supplied is not a database disk."
71 */
72 raiserror 17471
73 return (1)
74 end
75
76 /* Do not allow this operation for a private device. */
77 if exists (select *
78 from master.dbo.sysdevices
79 where name = @logicalname
80 and (status2 & 2) = 2)
81 begin
82 raiserror 17473, @logicalname
83 return (1)
84 end
85
86 /* Do not allow this operation for a virtual cache device. */
87 if exists (select *
88 from master.dbo.sysdevices
89 where name = @logicalname
90 and (status2 & 8) = 8)
91 begin
92 raiserror 17474, @logicalname
93 return (1)
94 end
95
96 if @defstatus = "defaulton"
97 begin
98 update master.dbo.sysdevices
99 set status = status | 1
100 where name = @logicalname
101 return (0)
102 end
103
104 if @defstatus = "defaultoff"
105 begin
106 update master.dbo.sysdevices
107 set status = status & ~ 1
108 where name = @logicalname
109 return (0)
110 end
111
112 /*
113 ** @defstatus must be "defaulton" or "defaultoff"
114 */
115 /*
116 ** 17472, "Usage: sp_diskdefault logicalname {defaulton | defaultoff}."
117 */
118 raiserror 17472
119 return (1)
120
exec sp_procxmode 'sp_diskdefault', 'AnyMode'
go
Grant Execute on sp_diskdefault to public
go
MGTP 3 Grant to public master..sysdevices
MSUB 2 Subquery Marker 