VYQwRQzv

1------------------------------------------------------------------
2--
3-- Setup misc databases
4-- 360AgentV15
5--
6------------------------------------------------------------------
7if exists (select * from sys.sysdatabases where name = '360AgentV15')
8begin
9use [360AgentV15]
10
11print '########Start: creating users and applying roles to 360AgentV15#######'
12
13exec sp_dbcmptlevel [360AgentV15], 100
14
15if (select SCHEMA_ID('360SrvcAgnt')) is not null DROP SCHEMA [360SrvcAgnt]
16
17if exists (select * from sys.sql_logins where name = N'360SrvcAgnt') DROP LOGIN [360SrvcAgnt]
18
19create login [360SrvcAgnt] with password='1adt4u', default_database=[master], default_language=[us_english], CHECK_POLICY = OFF
20
21-------------------------------------------------------
22--Add database logins
23-------------------------------------------------------
24if (select DATABASE_PRINCIPAL_ID(N'360SrvcAgnt')) is not null
25begin
26drop user [360SrvcAgnt]
27end
28
29create user [360SrvcAgnt] for login [360SrvcAgnt]
30
31-------------------------------------------------------
32--Add the roles and assign logins
33-------------------------------------------------------
34if (select DATABASE_PRINCIPAL_ID(N'360SrvcAgntRole')) is null
35begin
36create role [360SrvcAgntRole]
37end
38
39exec sp_addrolemember [360SrvcAgntRole], [360SrvcAgnt]
40
41--select 'exec spg_AddGrants ''' + rtrim(name) + '''' from sysobjects where xtype='P' and name not like 'dt%'
42exec spg_AddGrants 'spg_AddGrants'
43exec spg_AddGrants 'spAddRequest'
44exec spg_AddGrants 'spAllotSA'
45exec spg_AddGrants 'spCancelRequest'
46exec spg_AddGrants 'spCheckFriendlyName'
47exec spg_AddGrants 'spClearResults'
48exec spg_AddGrants 'spDeleteOldRequests'
49exec spg_AddGrants 'spGetAllSAStatus'
50exec spg_AddGrants 'spGetRequestStatus'
51exec spg_AddGrants 'spGetResults'
52exec spg_AddGrants 'spGetResultsCount'
53exec spg_AddGrants 'spGetResultsForSAID'
54exec spg_AddGrants 'spGetSADetails'
55exec spg_AddGrants 'spGetSAMasterData'
56exec spg_AddGrants 'spGetSAParams'
57exec spg_AddGrants 'spGetServiceAgentRetryCount'
58exec spg_AddGrants 'spGetServiceAgentStatus'
59exec spg_AddGrants 'spGetServiceAgentTimeToLive'
60exec spg_AddGrants 'spInsertServiceAgentDetails'
61exec spg_AddGrants 'spInsertServiceAgentParameters'
62exec spg_AddGrants 'spRecoverServiceAgents'
63exec spg_AddGrants 'spUpdateAllotedRequest'
64exec spg_AddGrants 'spUpdateAllotedServiceAgent'
65exec spg_AddGrants 'spUpdateReqStatusComplete'
66exec spg_AddGrants 'spUpdateReqStatusInComplete'
67exec spg_AddGrants 'spUpdateSADetails'
68exec spg_AddGrants 'spUpdateStatus'
69exec spg_AddGrants 'spValidateRequestID'
70exec spg_AddGrants 'spVerifyReferenceKey'
71exec spg_AddGrants 'spVerifyRequestID'
72
73print '########End: creating users and applying roles to 360AgentV15#######'
74print ''; print ''; print ''; print '';
75
76
77end
78
79------------------------------------------------------------------
80--
81-- Setup misc databases
82-- LogAuth
83--
84------------------------------------------------------------------
85if exists (select * from sys.sysdatabases where name = 'logauth')
86begin
87use LogAuth
88
89print '########Start: LogAuth upgrade#######'
90
91exec sp_dbcmptlevel LogAuth, 100
92
93if (select SCHEMA_ID('AMSAuditService')) is not null DROP SCHEMA [AMSAuditService]
94
95if exists (select * from sys.sql_logins where name = N'AMSAuditService') DROP LOGIN [AMSAuditService]
96
97create login [AMSAuditService] with password='We8TacoTime', default_database=[master], default_language=[us_english], CHECK_POLICY = OFF
98
99-------------------------------------------------------
100--Add database logins
101-------------------------------------------------------
102if (select DATABASE_PRINCIPAL_ID(N'AMSAuditService')) is not null
103begin
104drop user [AMSAuditService]
105end
106
107create user [AMSAuditService] for login [AMSAuditService]
108
109-------------------------------------------------------
110--Add the roles and assign logins
111-------------------------------------------------------
112if (select DATABASE_PRINCIPAL_ID(N'AMSAuditService_Role')) is null
113begin
114create role [AMSAuditService_Role]
115end
116
117exec sp_addrolemember [AMSAuditService_Role], [AMSAuditService]
118
119GRANT EXECUTE ON [dbo].[spi_LoginActivity] TO [AMSAuditService_Role]
120GRANT EXECUTE ON [dbo].[sps_LoginActivityReport] TO [AMSAuditService_Role]
121GRANT EXECUTE ON [dbo].[sps_LoginActivityUnsuccessfulTotalReport] TO [AMSAuditService_Role]
122
123print '########End: LogAuth upgrade#######'
124print ''; print ''; print ''; print '';
125
126end
127
128
129------------------------------------------------------------------
130--
131-- Create database server logins. Same as SiteDBServerLogins.sqs
132--
133------------------------------------------------------------------
134-- Change SiteDb to 100 compatibilty
135
136print '########Now siteDB and other agency DBs#######'
137
138use SiteDb
139exec sp_dbcmptlevel SiteDb, 100
140
141if (select SCHEMA_ID('ACC')) is not null DROP SCHEMA [ACC]
142if (select SCHEMA_ID('AFW')) is not null DROP SCHEMA [AFW]
143if (select SCHEMA_ID('AFWDBO')) is not null DROP SCHEMA [AFWDBO]
144if (select SCHEMA_ID('AFWUSER')) is not null DROP SCHEMA [AFWUSER]
145if (select SCHEMA_ID('AFWWEBSVC')) is not null DROP SCHEMA [AFWWEBSVC]
146if (select SCHEMA_ID('S247CallCenter')) is not null DROP SCHEMA [S247CallCenter]
147if (select SCHEMA_ID('AFWDNLDROLE')) is not null DROP SCHEMA [AFWDNLDROLE]
148
149
150
151
152use SiteDB
153
154--AMS_PRODUCTVERSIONSCRIPT.SQI content is included here; it is long---------------------->
155--AMS_PRODUCTVERSIONSCRIPT.SQI content is included here; it is long---------------------->
156--AMS_PRODUCTVERSIONSCRIPT.SQI content is included here; it is long---------------------->
157--AMS_PRODUCTVERSIONSCRIPT.SQI content is included here; it is long---------------------->
158
159--***************************************************************************
160--* $Author: Albrecne $
161--* $Date: 10/12/05 3:42p $
162--* $Revision: 2 $
163--* $NoKeywords: $
164--***************************************************************************
165--***************************************************************************
166--*
167--* File Name: AMS_PRODUCTVERSIONSCRIPT.SQI
168--*
169--* Table:
170--* AMS_ProductVersionScript
171--*
172--*
173--* Description:
174--* Inserts appropriate initial data for AMS_ProductVersionScript
175--*
176--* Notes:
177--*
178--* Modifications:
179--*
180--**************************************************************************
181if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spa_ProductVersionScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
182exec [dbo].[spa_ProductVersionScript] 'D'
183GO
184
185delete from AMS_ProductVersionScript
186go
187
188---------------------------------------------------------------------------------------------------
189-- SQL2000 and SQL2005 Installs
190---------------------------------------------------------------------------------------------------
191---------------------------------------------------------------------------------------------------
192-- Database server security logins
193---------------------------------------------------------------------------------------------------
194insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
195values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'Security', 1, 'Admin', 'AMS 360 v 1.0 server level security', '
196
197declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N''master'', @loginlang = N''us_english''
198
199if not exists (select * from master.dbo.syslogins where loginname = N''AFW'')
200begin
201if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
202select @logindb = N''master''
203if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
204select @loginlang = @@language
205exec sp_addlogin N''AFW'', N''QWERTY'', @logindb, @loginlang
206end
207
208if not exists (select * from master.dbo.syslogins where loginname = N''AFWDBO'')
209begin
210if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
211select @logindb = N''master''
212if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
213select @loginlang = @@language
214exec sp_addlogin N''AFWDBO'', ''4th0Fju1Y'', @logindb, @loginlang
215end
216
217if not exists (select * from master.dbo.syslogins where loginname = N''AFWUSER'')
218begin
219if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
220select @logindb = N''master''
221if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
222select @loginlang = @@language
223exec sp_addlogin N''AFWUSER'', ''3BlinDm1Ce'', @logindb, @loginlang
224end
225
226if not exists (select * from master.dbo.syslogins where loginname = N''AFWWEBSVC'')
227begin
228if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
229select @logindb = N''master''
230if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
231select @loginlang = @@language
232exec sp_addlogin N''AFWWEBSVC'', ''Tw0=)4U'', @logindb, @loginlang
233end
234
235if not exists (select * from master.dbo.syslogins where loginname = N''S247CallCenter'')
236begin
237if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
238select @logindb = N''master''
239if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
240select @loginlang = @@language
241exec sp_addlogin N''S247CallCenter'', ''G6M2r4n3r7'', @logindb, @loginlang
242end','^^^', getdate(), getdate())
243
244
245insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
246values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'Security', 100, 'Admin', 'AMS 360 v 1.0 server level security', '
247
248declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N''master'', @loginlang = N''us_english''
249
250if not exists (select * from sys.sql_logins where name = N''AFW'')
251begin
252if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
253select @logindb = N''master''
254if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
255select @loginlang = @@language
256create login AFW with password=''QWERTY'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
257end
258
259if not exists (select * from sys.sql_logins where name = N''AFWDBO'')
260begin
261if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
262select @logindb = N''master''
263if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
264select @loginlang = @@language
265create login AFWDBO with password=''4th0Fju1Y'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
266end
267
268if not exists (select * from sys.sql_logins where name = N''AFWUSER'')
269begin
270if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
271select @logindb = N''master''
272if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
273select @loginlang = @@language
274create login AFWUSER with password=''3BlinDm1Ce'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
275end
276
277if not exists (select * from sys.sql_logins where name = N''AFWWEBSVC'')
278begin
279if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
280select @logindb = N''master''
281if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
282select @loginlang = @@language
283create login AFWWEBSVC with password=''Tw0=)4U'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
284end
285
286if not exists (select * from sys.sql_logins where name = N''S247CallCenter'')
287begin
288if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
289select @logindb = N''master''
290if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
291select @loginlang = @@language
292create login S247CallCenter with password=''G6M2r4n3r7'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
293end','^^^', getdate(), getdate())
294
295
296
297
298
299---------------------------------------------------------------------------------------------------
300-- SQL2000
301---------------------------------------------------------------------------------------------------
302---------------------------------------------------------------------------------------------------
303-- AMS 360 database security logins
304---------------------------------------------------------------------------------------------------
305insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
306values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'Security', 2, 'Admin', 'AMS 360 v 1.0 database security roles', '-------------------------------------------------------
307--Add database logins
308-------------------------------------------------------
309if exists (select * from dbo.sysusers where name = N''AFW'' and issqluser = 1)
310EXEC sp_revokedbaccess N''AFW''
311EXEC sp_grantdbaccess N''AFW'', N''AFW''
312
313if exists (select * from dbo.sysusers where name = N''AFWDBO'' and issqluser = 1)
314EXEC sp_revokedbaccess N''AFWDBO''
315EXEC sp_grantdbaccess N''AFWDBO'', N''AFWDBO''
316
317if exists (select * from dbo.sysusers where name = N''AFWUSER'' and issqluser = 1)
318EXEC sp_revokedbaccess N''AFWUSER''
319EXEC sp_grantdbaccess N''AFWUSER'', N''AFWUSER''
320
321if exists (select * from dbo.sysusers where name = N''AFWWEBSVC'' and issqluser = 1)
322EXEC sp_revokedbaccess N''AFWWEBSVC''
323EXEC sp_grantdbaccess N''AFWWEBSVC'', N''AFWWEBSVC''
324
325if exists (select * from dbo.sysusers where name = N''S247CallCenter'' and issqluser = 1)
326EXEC sp_revokedbaccess N''S247CallCenter''
327EXEC sp_grantdbaccess N''S247CallCenter'', N''S247CallCenter''
328
329-------------------------------------------------------
330--Download Application Role
331--Needs to be applied to all agency databases
332--Gary Schuster is programmer, Cameron added
333-------------------------------------------------------
334if exists (select * from dbo.sysusers where name = ''AFWDNLDROLE'' and isapprole = 1)
335exec sp_dropapprole AFWDNLDROLE
336
337-------------------------------------------------------
338--SQL2005 syntax not used because there is no option
339--to remove strong password checking.
340--As long as we are supporting both 2000 and 2005 servers
341--strong password checking cannot be enabled.
342--
343--Thus, the 2000 syntax is used which does not enforce
344--strong passwords.
345-------------------------------------------------------
346--2000 syntax
347exec sp_addapprole AFWDNLDROLE, ''downit''
348
349-------------------------------------------------------
350--Add the roles and assign logins
351-------------------------------------------------------
352if not exists (select * from dbo.sysusers where name = N''AFWAPPROLE'' and issqlrole = 1)
353EXEC sp_addrole N''AFWAPPROLE''
354
355if not exists (select * from dbo.sysusers where name = N''AFWDBOROLE'' and issqlrole = 1)
356EXEC sp_addrole N''AFWDBOROLE''
357
358if not exists (select * from dbo.sysusers where name = N''AFWROLE'' and issqlrole = 1)
359EXEC sp_addrole N''AFWROLE''
360
361if not exists (select * from dbo.sysusers where name = N''AFWUSERROLE'' and issqlrole = 1)
362EXEC sp_addrole N''AFWUSERROLE''
363
364if not exists (select * from dbo.sysusers where name = N''AFWWEBSVCROLE'' and issqlrole = 1)
365EXEC sp_addrole N''AFWWEBSVCROLE''
366
367if not exists (select * from dbo.sysusers where name = N''BIROLE'' and issqlrole = 1)
368EXEC sp_addrole N''BIROLE''
369
370if not exists (select * from dbo.sysusers where name = N''S247CallCenterRole'' and issqlrole = 1)
371EXEC sp_addrole N''S247CallCenterRole''
372
373
374exec sp_addrolemember N''AFWAPPROLE'', N''AFWDNLDROLE''
375
376exec sp_addrolemember N''AFWDBOROLE'', N''AFWDBO''
377
378exec sp_addrolemember N''AFWROLE'', N''AFW''
379
380exec sp_addrolemember N''AFWUSERROLE'', N''AFWUSER''
381
382exec sp_addrolemember N''AFWWEBSVCROLE'', N''AFWWEBSVC''
383
384exec sp_addrolemember N''S247CallCenterRole'', N''S247CallCenter''
385
386
387------------------------------------------------------
388-- To support dynamic SQL
389------------------------------------------------------
390exec sp_addrolemember N''db_owner'', N''AFWDBOROLE''
391
392exec sp_addrolemember N''db_owner'', N''AFWUSERROLE''
393','^^^', getdate(), getdate())
394GO
395
396------------------------------------------------------
397-- SQL2005
398------------------------------------------------------
399insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
400values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'Security', 200, 'Admin', 'AMS 360 v 1.0 database security roles', '-------------------------------------------------------
401--Add database logins
402-------------------------------------------------------
403if exists (select * from dbo.sysusers where name = N''AFW'' and issqluser = 1)
404EXEC sp_revokedbaccess N''AFW''
405EXEC sp_grantdbaccess N''AFW'', N''AFW''
406
407if exists (select * from dbo.sysusers where name = N''AFWDBO'' and issqluser = 1)
408EXEC sp_revokedbaccess N''AFWDBO''
409EXEC sp_grantdbaccess N''AFWDBO'', N''AFWDBO''
410
411if exists (select * from dbo.sysusers where name = N''AFWUSER'' and issqluser = 1)
412EXEC sp_revokedbaccess N''AFWUSER''
413EXEC sp_grantdbaccess N''AFWUSER'', N''AFWUSER''
414
415if exists (select * from dbo.sysusers where name = N''AFWWEBSVC'' and issqluser = 1)
416EXEC sp_revokedbaccess N''AFWWEBSVC''
417EXEC sp_grantdbaccess N''AFWWEBSVC'', N''AFWWEBSVC''
418
419if exists (select * from dbo.sysusers where name = N''S247CallCenter'' and issqluser = 1)
420EXEC sp_revokedbaccess N''S247CallCenter''
421EXEC sp_grantdbaccess N''S247CallCenter'', N''S247CallCenter''
422
423-------------------------------------------------------
424--Download Application Role
425--Needs to be applied to all agency databases
426--Gary Schuster is programmer, Cameron added
427-------------------------------------------------------
428--SQL2005 server - must drop schema first. Schema created
429--automatically when using SQL2000 command below.
430if (select SCHEMA_ID(''AFWDNLDROLE'')) is not null
431DROP SCHEMA [AFWDNLDROLE]
432
433if exists (select * from dbo.sysusers where name = ''AFWDNLDROLE'' and isapprole = 1)
434exec sp_dropapprole AFWDNLDROLE
435
436-------------------------------------------------------
437--SQL2005 syntax not used because there is no option
438--to remove strong password checking.
439--As long as we are supporting both 2000 and 2005 servers
440--strong password checking cannot be enabled.
441--
442--Thus, the 2000 syntax is used which does not enforce
443--strong passwords.
444-------------------------------------------------------
445--2000 syntax
446exec sp_addapprole AFWDNLDROLE, ''downit''
447
448-- Cleanup the schema created by the SQL2000 command.
449if (select SCHEMA_ID(''AFWDNLDROLE'')) is not null
450DROP SCHEMA [AFWDNLDROLE]
451
452-------------------------------------------------------
453--Add the roles and assign logins
454-------------------------------------------------------
455if not exists (select * from dbo.sysusers where name = N''AFWAPPROLE'' and issqlrole = 1)
456EXEC sp_addrole N''AFWAPPROLE''
457
458if not exists (select * from dbo.sysusers where name = N''AFWDBOROLE'' and issqlrole = 1)
459EXEC sp_addrole N''AFWDBOROLE''
460
461if not exists (select * from dbo.sysusers where name = N''AFWROLE'' and issqlrole = 1)
462EXEC sp_addrole N''AFWROLE''
463
464if not exists (select * from dbo.sysusers where name = N''AFWUSERROLE'' and issqlrole = 1)
465EXEC sp_addrole N''AFWUSERROLE''
466
467if not exists (select * from dbo.sysusers where name = N''AFWWEBSVCROLE'' and issqlrole = 1)
468EXEC sp_addrole N''AFWWEBSVCROLE''
469
470if not exists (select * from dbo.sysusers where name = N''BIROLE'' and issqlrole = 1)
471EXEC sp_addrole N''BIROLE''
472
473if not exists (select * from dbo.sysusers where name = N''S247CallCenterRole'' and issqlrole = 1)
474EXEC sp_addrole N''S247CallCenterRole''
475
476
477exec sp_addrolemember N''AFWAPPROLE'', N''AFWDNLDROLE''
478
479exec sp_addrolemember N''AFWDBOROLE'', N''AFWDBO''
480
481exec sp_addrolemember N''AFWROLE'', N''AFW''
482
483exec sp_addrolemember N''AFWUSERROLE'', N''AFWUSER''
484
485exec sp_addrolemember N''AFWWEBSVCROLE'', N''AFWWEBSVC''
486
487exec sp_addrolemember N''S247CallCenterRole'', N''S247CallCenter''
488
489
490------------------------------------------------------
491-- To support dynamic SQL
492------------------------------------------------------
493exec sp_addrolemember N''db_owner'', N''AFWDBOROLE''
494
495exec sp_addrolemember N''db_owner'', N''AFWUSERROLE''
496','^^^', getdate(), getdate())
497GO
498
499
500---------------------------------------------------------------------------------------------------
501-- Error messages stored in Sysmessages table
502---------------------------------------------------------------------------------------------------
503insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
504values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'Sysmessage', 1, 'Admin', 'AMS 360 v 1.0 system error messages', 'exec sp_addmessage 50001,16,''AMS 360 Error: Query returned a rowcount of 0'',@replace=REPLACE
505exec sp_addmessage 50002,16,''AMS 360 Error: Failed getting a unique id from SequenceDate table'',@replace=REPLACE
506exec sp_addmessage 50003,16,''AMS 360 Error: Failed creating a unique code'',@replace=REPLACE
507exec sp_addmessage 50004,16,''AMS 360 Error: Failed getting a new Coverage Code key'',@replace=REPLACE
508exec sp_addmessage 50005,16,''AMS 360 Error: Failed updating or deleting an employee'',@replace=REPLACE
509exec sp_addmessage 50006,16,''AMS 360 Error: Invalid AMS 360 LogIn'',@replace=REPLACE
510exec sp_addmessage 50007,16,''AMS 360 Error: Invalid AMS 360 Password'',@replace=REPLACE
511exec sp_addmessage 50008,16,''AMS 360 Error: Auth User'',@replace=REPLACE
512exec sp_addmessage 50009,16,''AMS 360 Error: Auth Pass DB'',@replace=REPLACE
513exec sp_addmessage 50010,16,''AMS 360 Error: Auth LogIn DB'',@replace=REPLACE
514exec sp_addmessage 50011,16,''AMS 360 Error: Failed creating a GL Department Code'',@replace=REPLACE
515exec sp_addmessage 50012,16,''AMS 360 Error: Failed deleting a GL Account'',@replace=REPLACE
516exec sp_addmessage 50013,16,''AMS 360 Error: Failed updating or deleting an Agency License'',@replace=REPLACE
517exec sp_addmessage 50014,16,''AMS 360 Error: Failed getting the current Fiscal Year'',@replace=REPLACE
518exec sp_addmessage 50015,16,''AMS 360 Error: Failed getting the next Fiscal Year'',@replace=REPLACE
519exec sp_addmessage 50016,16,''AMS 360 Error: Failed deleting Pr String'',@replace=REPLACE
520exec sp_addmessage 50017,16,''AMS 360 Error: Failed deleting Setup Billing Transaction'',@replace=REPLACE
521exec sp_addmessage 50018,16,''AMS 360 Error: Invalid Endorsement Effective Date'',@replace=REPLACE
522exec sp_addmessage 50019,16,''AMS 360 Error: Trigger Failed deleting Basic Policy'',@replace=REPLACE
523exec sp_addmessage 50020,16,''AMS 360 Error: Cust Id'',@replace=REPLACE
524exec sp_addmessage 50021,16,''AMS 360 Error: SPS TREL by PolId'',@replace=REPLACE
525exec sp_addmessage 50022,16,''AMS 360 Error: SPU BPOL'',@replace=REPLACE
526exec sp_addmessage 50023,16,''AMS 360 Error: Query had an error and the rowcount <> 0'',@replace=REPLACE
527exec sp_addmessage 50024,16,''AMS 360 Error: Failed updating the Invoice in the GLT trigger'',@replace=REPLACE
528exec sp_addmessage 50025,16,''AMS 360 Error: Failed updating the Customer in the GLT trigger'',@replace=REPLACE
529exec sp_addmessage 50026,16,''AMS 360 Error: Failed updating the Broker in the GLT trigger'',@replace=REPLACE
530exec sp_addmessage 50027,16,''AMS 360 Error: ActivityAction was deleted but the action still exists in Transaction and Suspense'',@replace=REPLACE
531exec sp_addmessage 50028,16,''AMS 360 Error: Auth Group was deleted but the AGrpId still exists in CustXRef'',@replace=REPLACE
532exec sp_addmessage 50029,16,''AMS 360 Error: ASTAT Delete'',@replace=REPLACE
533exec sp_addmessage 50030,16,''AMS 360 Error: AgencyXRefType was deleted but the AXRefId still exists in AUSER'',@replace=REPLACE
534exec sp_addmessage 50031,16,''AMS 360 Error: Failed in the Policy Tran trigger'',@replace=REPLACE
535exec sp_addmessage 50032,16,''AMS 360 Error: Failed in the Policy Company trigger'',@replace=REPLACE
536exec sp_addmessage 50033,16,''AMS 360 Error: ANOT Delete'',@replace=REPLACE
537exec sp_addmessage 50034,16,''AMS 360 Error: Failed updating the LOBTran in trigger'',@replace=REPLACE
538exec sp_addmessage 50035,16,''AMS 360 Error: Failed retrieving a text pointer for an existing text field'',@replace=REPLACE
539exec sp_addmessage 50199,16,''AMS 360 Error: GL Division Code is null'',@replace=REPLACE
540exec sp_addmessage 50200,16,''AMS 360 Error: SQL High Value'',@replace=REPLACE
541exec sp_addmessage 50201,16,''AMS 360 Error: Failed to delete Invoice reference in the GLT trigger'',@replace=REPLACE','^^^', getdate(), getdate())
542go
543
544
545insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
546values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'ServerSettings', 1, 'Admin', 'SQL Server Settings', '-- Server Settings Follow
547-- To allow advanced options to be changed.
548exec sp_configure ''show advanced options'', 1
549reconfigure
550
551-- To enable the feature. xp_cmdshell is required by NPR and migration)
552exec sp_configure ''xp_cmdshell'', 1
553reconfigure
554
555-- (Note: CONCAT_NULL_YEILDS_NULL and NUMERIC_ROUNDABORT
556-- settings are not available via the Enterprise Manager interface)
557exec sp_configure ''User Option'', 5500
558reconfigure
559
560-- Sets the nested triggers option
561exec sp_configure ''Nested Triggers'', 1
562reconfigure
563
564-- Sets the “enforce distributed transactions (MTS)” option
565exec sp_configure ''Remote Proc Trans'', 1
566reconfigure
567
568-- Sets the remote server connections timeout option
569exec sp_configure ''Remote Query Timeout'', 600
570reconfigure
571
572-- Set parallelism to 1 which is typically the best setting for performance
573exec sp_configure ''max degree of parallelism'', 1
574reconfigure
575
576-- Allow OLE Automation (required by SiteDB GetServer function)
577exec sp_configure ''Ole Automation Procedures'', 1
578reconfigure','^^^', getdate(), getdate())
579GO
580insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
581values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'EndPointCreation', 1, 'Admin', 'Creates EndPoint on SQL Server Instance', ' --This Script Creates an Endpoint at the Server Level
582
583IF NOT EXISTS (Select * from Sys.endpoints where [Name] = ''AMS360.ONS.EndPoint.Agency'') 
584and NOT EXISTS (Select * from Sys.endpoints where [Name] = ''AMS360.ONS.EndPoint.Initiator'') 
585begin
586CREATE ENDPOINT [AMS360.ONS.EndPoint.Agency]
587STATE=STARTED AS TCP (LISTENER_PORT = 4023, LISTENER_IP = ALL)
588FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED, AUTHENTICATION = WINDOWS , ENCRYPTION = SUPPORTED )
589
590grant connect on endpoint::[AMS360.ONS.EndPoint.Agency] to public
591end
592','^^^', getdate(), getdate())
593GO
594
595
596---------------------------------------------------------------------------------------------------
597-- **NOTE** **NOTE** **NOTE** **NOTE** **NOTE** **NOTE** **NOTE** **NOTE** 
598-- 
599-- This insert is inserting rows for SiteDB logins and is DIFFERENT then the insert above for ams360 agent databases.
600-- This insert only needs either the Sql2000 or 2005 syntax in the table since SiteDB is only one or the other version.
601-- The insert for ams360 agent databases needs both inserted into the table so when the insert is executed the Sql version
602-- is determined at run time.
603-- 
604-- 
605-- **NOTE** **NOTE** **NOTE** **NOTE** **NOTE** **NOTE** **NOTE** **NOTE** 
606---------------------------------------------------------------------------------------------------
607
608---------------------------------------------------------------------------------------------------
609-- SQL2000 and SQL2005 Installs
610---------------------------------------------------------------------------------------------------
611---------------------------------------------------------------------------------------------------
612-- SiteDB server security logins
613---------------------------------------------------------------------------------------------------
614insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
615values (dbo.fnc_GetConstant('PRODUCTID_ACC'), '1.0.1281.1', 'Security', 1, 'Admin', 'SiteDB server level security', '
616
617declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N''master'', @loginlang = N''us_english''
618
619if not exists (select * from master.dbo.syslogins where loginname = N''AFW'')
620BEGIN
621if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
622select @logindb = N''master''
623if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
624select @loginlang = @@language
625exec sp_addlogin N''AFW'', N''QWERTY'', @logindb, @loginlang
626END
627
628if not exists (select * from master.dbo.syslogins where loginname = N''AFWDBO'')
629BEGIN
630if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
631select @logindb = N''master''
632if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
633select @loginlang = @@language
634exec sp_addlogin N''AFWDBO'', ''4th0Fju1Y'', @logindb, @loginlang
635END
636
637if not exists (select * from master.dbo.syslogins where loginname = N''AFWUSER'')
638BEGIN
639if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
640select @logindb = N''master''
641if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
642select @loginlang = @@language
643exec sp_addlogin N''AFWUSER'', ''3BlinDm1Ce'', @logindb, @loginlang
644END
645
646if not exists (select * from master.dbo.syslogins where loginname = N''AFWWEBSVC'')
647BEGIN
648if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
649select @logindb = N''master''
650if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
651select @loginlang = @@language
652exec sp_addlogin N''AFWWEBSVC'', ''Tw0=)4U'', @logindb, @loginlang
653END
654
655if not exists (select * from master.dbo.syslogins where loginname = N''S247CallCenter'')
656BEGIN
657if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
658select @logindb = N''master''
659if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
660select @loginlang = @@language
661exec sp_addlogin N''S247CallCenter'', ''G6M2r4n3r7'', @logindb, @loginlang
662END
663
664if not exists (select * from master.dbo.syslogins where loginname = N''ACC'')
665BEGIN
666if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
667select @logindb = N''SiteDB''
668if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
669select @loginlang = @@language
670exec sp_addlogin N''ACC'', ''b12-Vitamen'', @logindb, @loginlang
671END','^^^', getdate(), getdate())
672
673
674insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
675values (dbo.fnc_GetConstant('PRODUCTID_ACC'), '1.0.1281.1', 'Security', 100, 'Admin', 'AMS 360 v 1.0 server level security', '
676
677declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N''master'', @loginlang = N''us_english''
678
679if not exists (select * from sys.sql_logins where name = N''AFW'')
680begin
681if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
682select @logindb = N''master''
683if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
684select @loginlang = @@language
685create login AFW with password=''QWERTY'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
686end
687
688if not exists (select * from sys.sql_logins where name = N''AFWDBO'')
689begin
690if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
691select @logindb = N''master''
692if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
693select @loginlang = @@language
694create login AFWDBO with password=''4th0Fju1Y'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
695end
696
697if not exists (select * from sys.sql_logins where name = N''AFWUSER'')
698begin
699if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
700select @logindb = N''master''
701if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
702select @loginlang = @@language
703create login AFWUSER with password=''3BlinDm1Ce'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
704end
705
706if not exists (select * from sys.sql_logins where name = N''AFWWEBSVC'')
707begin
708if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
709select @logindb = N''master''
710if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
711select @loginlang = @@language
712create login AFWWEBSVC with password=''Tw0=)4U'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
713end
714
715if not exists (select * from sys.sql_logins where name = N''S247CallCenter'')
716begin
717if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
718select @logindb = N''master''
719if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
720select @loginlang = @@language
721create login S247CallCenter with password=''G6M2r4n3r7'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
722end
723
724if not exists (select * from master.dbo.syslogins where loginname = N''ACC'')
725BEGIN
726if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
727select @logindb = N''SiteDB''
728if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N''us_english'')
729select @loginlang = @@language
730create login ACC with password=''b12-Vitamen'', default_database=master, default_language=us_english, CHECK_POLICY = OFF
731end','^^^', getdate(), getdate())
732
733
734
735---------------------------------------------------------------------------------------------------
736-- SQL2000
737---------------------------------------------------------------------------------------------------
738---------------------------------------------------------------------------------------------------
739-- AMS 360 database security logins
740---------------------------------------------------------------------------------------------------
741insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
742values (dbo.fnc_GetConstant('PRODUCTID_ACC'), '1.0.1281.1', 'Security', 2, 'Admin', 'SiteDB database security roles', '-------------------------------------------------------
743--Add database logins
744-------------------------------------------------------
745if exists (select * from dbo.sysusers where name = N''ACC'' and issqluser = 1)
746EXEC sp_revokedbaccess N''ACC''
747EXEC sp_grantdbaccess N''ACC'', N''ACC''
748
749if exists (select * from dbo.sysusers where name = N''AFW'' and issqluser = 1)
750EXEC sp_revokedbaccess N''AFW''
751EXEC sp_grantdbaccess N''AFW'', N''AFW''
752
753if exists (select * from dbo.sysusers where name = N''AFWDBO'' and issqluser = 1)
754EXEC sp_revokedbaccess N''AFWDBO''
755EXEC sp_grantdbaccess N''AFWDBO'', N''AFWDBO''
756
757if exists (select * from dbo.sysusers where name = N''AFWUSER'' and issqluser = 1)
758EXEC sp_revokedbaccess N''AFWUSER''
759EXEC sp_grantdbaccess N''AFWUSER'', N''AFWUSER''
760
761if exists (select * from dbo.sysusers where name = N''AFWWEBSVC'' and issqluser = 1)
762EXEC sp_revokedbaccess N''AFWWEBSVC''
763EXEC sp_grantdbaccess N''AFWWEBSVC'', N''AFWWEBSVC''
764
765if exists (select * from dbo.sysusers where name = N''S247CallCenter'' and issqluser = 1)
766EXEC sp_revokedbaccess N''S247CallCenter''
767EXEC sp_grantdbaccess N''S247CallCenter'', N''S247CallCenter''
768
769if exists (select * from dbo.sysusers where name = ''AFWDNLDROLE'' and isapprole = 1)
770exec sp_dropapprole AFWDNLDROLE
771
772--2000 syntax
773exec sp_addapprole AFWDNLDROLE, ''downit''
774
775--SQL2005 server - must drop schema. Schema created
776--automatically when using SQL2000 command.
777if (select SCHEMA_ID(''AFWDNLDROLE'')) is not null
778DROP SCHEMA [AFWDNLDROLE]
779
780-------------------------------------------------------
781--Add the roles and assign logins
782-------------------------------------------------------
783if not exists (select * from dbo.sysusers where name = N''ACCROLE'' and issqlrole = 1)
784EXEC sp_addrole N''ACCROLE''
785
786if not exists (select * from dbo.sysusers where name = N''AFWAPPROLE'' and issqlrole = 1)
787EXEC sp_addrole N''AFWAPPROLE''
788
789if not exists (select * from dbo.sysusers where name = N''AFWDBOROLE'' and issqlrole = 1)
790EXEC sp_addrole N''AFWDBOROLE''
791
792if not exists (select * from dbo.sysusers where name = N''AFWROLE'' and issqlrole = 1)
793EXEC sp_addrole N''AFWROLE''
794
795if not exists (select * from dbo.sysusers where name = N''AFWUSERROLE'' and issqlrole = 1)
796EXEC sp_addrole N''AFWUSERROLE''
797
798if not exists (select * from dbo.sysusers where name = N''AFWWEBSVCROLE'' and issqlrole = 1)
799EXEC sp_addrole N''AFWWEBSVCROLE''
800
801if not exists (select * from dbo.sysusers where name = N''BIROLE'' and issqlrole = 1)
802EXEC sp_addrole N''BIROLE''
803
804if not exists (select * from dbo.sysusers where name = N''S247CallCenterRole'' and issqlrole = 1)
805EXEC sp_addrole N''S247CallCenterRole''
806
807
808exec sp_addrolemember N''ACCROLE'', N''ACC''
809
810exec sp_addrolemember N''AFWAPPROLE'', N''AFWDNLDROLE''
811
812exec sp_addrolemember N''AFWDBOROLE'', N''AFWDBO''
813
814exec sp_addrolemember N''AFWROLE'', N''AFW''
815
816exec sp_addrolemember N''AFWUSERROLE'', N''AFWUSER''
817
818exec sp_addrolemember N''AFWWEBSVCROLE'', N''AFWWEBSVC''
819
820exec sp_addrolemember N''S247CallCenterRole'', N''S247CallCenter''
821
822------------------------------------------------------
823-- To give ACC sa-like permissions across the SQL Instance
824------------------------------------------------------
825exec sp_addsrvrolemember ''ACC'', ''sysadmin''
826
827------------------------------------------------------
828-- To support dynamic SQL
829------------------------------------------------------
830exec sp_addrolemember N''db_owner'', N''ACCROLE''
831
832exec sp_addrolemember N''db_owner'', N''AFWDBOROLE''
833
834exec sp_addrolemember N''db_owner'', N''AFWUSERROLE''
835','^^^', getdate(), getdate())
836GO
837
838---------------------------------------------------------------------------------------------------
839-- SQL2005
840---------------------------------------------------------------------------------------------------
841---------------------------------------------------------------------------------------------------
842-- AMS 360 database security logins
843---------------------------------------------------------------------------------------------------
844insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
845values (dbo.fnc_GetConstant('PRODUCTID_ACC'), '1.0.1281.1', 'Security', 200, 'Admin', 'SiteDB database security roles', '-------------------------------------------------------
846--Add database logins
847-------------------------------------------------------
848if exists (select * from dbo.sysusers where name = N''ACC'' and issqluser = 1)
849EXEC sp_revokedbaccess N''ACC''
850EXEC sp_grantdbaccess N''ACC'', N''ACC''
851
852if exists (select * from dbo.sysusers where name = N''AFW'' and issqluser = 1)
853EXEC sp_revokedbaccess N''AFW''
854EXEC sp_grantdbaccess N''AFW'', N''AFW''
855
856if exists (select * from dbo.sysusers where name = N''AFWDBO'' and issqluser = 1)
857EXEC sp_revokedbaccess N''AFWDBO''
858EXEC sp_grantdbaccess N''AFWDBO'', N''AFWDBO''
859
860if exists (select * from dbo.sysusers where name = N''AFWUSER'' and issqluser = 1)
861EXEC sp_revokedbaccess N''AFWUSER''
862EXEC sp_grantdbaccess N''AFWUSER'', N''AFWUSER''
863
864if exists (select * from dbo.sysusers where name = N''AFWWEBSVC'' and issqluser = 1)
865EXEC sp_revokedbaccess N''AFWWEBSVC''
866EXEC sp_grantdbaccess N''AFWWEBSVC'', N''AFWWEBSVC''
867
868if exists (select * from dbo.sysusers where name = N''S247CallCenter'' and issqluser = 1)
869EXEC sp_revokedbaccess N''S247CallCenter''
870EXEC sp_grantdbaccess N''S247CallCenter'', N''S247CallCenter''
871
872if (select SCHEMA_ID(''AFWDNLDROLE'')) is not null
873DROP SCHEMA [AFWDNLDROLE]
874
875if exists (select * from dbo.sysusers where name = ''AFWDNLDROLE'' and isapprole = 1)
876exec sp_dropapprole AFWDNLDROLE
877
878--2000 syntax
879exec sp_addapprole AFWDNLDROLE, ''downit''
880
881if (select SCHEMA_ID(''AFWDNLDROLE'')) is not null
882DROP SCHEMA [AFWDNLDROLE]
883
884-------------------------------------------------------
885--Add the roles and assign logins
886-------------------------------------------------------
887if not exists (select * from dbo.sysusers where name = N''ACCROLE'' and issqlrole = 1)
888EXEC sp_addrole N''ACCROLE''
889
890if not exists (select * from dbo.sysusers where name = N''AFWAPPROLE'' and issqlrole = 1)
891EXEC sp_addrole N''AFWAPPROLE''
892
893if not exists (select * from dbo.sysusers where name = N''AFWDBOROLE'' and issqlrole = 1)
894EXEC sp_addrole N''AFWDBOROLE''
895
896if not exists (select * from dbo.sysusers where name = N''AFWROLE'' and issqlrole = 1)
897EXEC sp_addrole N''AFWROLE''
898
899if not exists (select * from dbo.sysusers where name = N''AFWUSERROLE'' and issqlrole = 1)
900EXEC sp_addrole N''AFWUSERROLE''
901
902if not exists (select * from dbo.sysusers where name = N''AFWWEBSVCROLE'' and issqlrole = 1)
903EXEC sp_addrole N''AFWWEBSVCROLE''
904
905if not exists (select * from dbo.sysusers where name = N''BIROLE'' and issqlrole = 1)
906EXEC sp_addrole N''BIROLE''
907
908if not exists (select * from dbo.sysusers where name = N''S247CallCenterRole'' and issqlrole = 1)
909EXEC sp_addrole N''S247CallCenterRole''
910
911
912exec sp_addrolemember N''ACCROLE'', N''ACC''
913
914exec sp_addrolemember N''AFWAPPROLE'', N''AFWDNLDROLE''
915
916exec sp_addrolemember N''AFWDBOROLE'', N''AFWDBO''
917
918exec sp_addrolemember N''AFWROLE'', N''AFW''
919
920exec sp_addrolemember N''AFWUSERROLE'', N''AFWUSER''
921
922exec sp_addrolemember N''AFWWEBSVCROLE'', N''AFWWEBSVC''
923
924exec sp_addrolemember N''S247CallCenterRole'', N''S247CallCenter''
925
926------------------------------------------------------
927-- To give ACC sa-like permissions across the SQL Instance
928------------------------------------------------------
929exec sp_addsrvrolemember ''ACC'', ''sysadmin''
930
931------------------------------------------------------
932-- To support dynamic SQL
933------------------------------------------------------
934exec sp_addrolemember N''db_owner'', N''ACCROLE''
935
936exec sp_addrolemember N''db_owner'', N''AFWDBOROLE''
937
938exec sp_addrolemember N''db_owner'', N''AFWUSERROLE''
939','^^^', getdate(), getdate())
940GO
941
942---------------------------------------------------------------------------------------------------
943-- Job Creation sproc - used to create sql server agent jobs
944-- Proc also located in $/Spider/1.5/Database/SQLServerConfiguration/
945---------------------------------------------------------------------------------------------------
946insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
947values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'JobCreationProc', 1, 'Admin', 'Creates procedure for job creation'
948,
949'
950IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[sp_AMSCreateAgentJob]'') AND type in (N''P'', N''PC''))
951BEGIN
952EXEC dbo.sp_executesql @statement = N''
953create proc [dbo].[sp_AMSCreateAgentJob]
954--***************************************************************************
955--* Type: StoredProc (Public)
956--* Name: sp_AMSCreateAgentJob
957--* Author: Cameron
958--* Desc: Creates a sql server agent job.
959--* Used by the AMS job scheduler.
960--*
961--* Parameters Description
962--* ---------------------------------------------------------------------------
963--* @JobName Name of the job. Name appears in SQL Server agent job listing.
964--* MUST BE UNIQUE!
965--* @JobDescription Description of Job
966--* @SprocName Name of sproc this job calls
967--* @TimeOfDayToRunSproc Time of day to run spro
968--* Is the time on any day between active_start_date and active_end_date
969--* to begin execution of the job. active_start_time is int, with a
970--* default of 000000, which indicates 12:00:00 A.M. on a 24-hour clock,
971--* and must be entered using the form HHMMSS.
972--* @DatabaseName Name of database where sproc is located
973--* Is the name of the database in which to execute a TSQL step.
974--* @ServerName Name of the server where job should run.
975--* Is the name of the server at which to target the job. Server can be
976--* either (LOCAL) for a local server, or the name of an existing target server.
977--* Optional Parms:
978--* @JobOwnerLoginName Is the name of the login that owns the job.
979--* NULL is interpreted as the current login name.
980--* @JobCategoryName Category job is listed under in SQL Server agent job listing.
981--* @freq_type 4 (default) = daily
982--* 32 = monthly
983--* @freq_relative_interval
984--* 0 (default) = daily
985--* 1 = 1st sunday
986--* 4 = 3rd sunday
987--* @freq_recurrence_factor
988--* 0 = daily
989--* 1 = monthly ?
990--*
991--**************************************************************************
992@JobName nvarchar(1000)
993, @JobDescription nvarchar(2000)
994, @SprocName nvarchar(256)
995, @DatabaseName nvarchar(256)= N''''SiteDB''''
996, @ServerName nvarchar(256)
997, @TimeOfDayToRunSproc int
998, @JobOwnerLoginName nvarchar(100) = null
999, @JobCategoryName nvarchar(1000) = N''''[Uncategorized (Local)]''''
1000, @freq_type int = 4 -- defaults to daily
1001, @freq_relative_interval int = 0
1002, @freq_recurrence_factor int = 0
1003
1004
1005--with encryption--
1006as
1007set nocount on
1008
1009declare
1010@ErrorNo int
1011, @CommandToExecute nvarchar(4000)
1012
1013select
1014@ErrorNo = 0
1015, @CommandToExecute = N''''exec '''' + @SprocName
1016
1017BEGIN TRANSACTION
1018DECLARE @JobID BINARY(16)
1019DECLARE @ReturnCode INT
1020SELECT @ReturnCode = 0
1021IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N''''[Uncategorized (Local)]'''') < 1
1022EXECUTE msdb.dbo.sp_add_category @name = N''''[Uncategorized (Local)]''''
1023
1024-- Delete the job with the same name (if it exists)
1025SELECT @JobID = job_id
1026FROM msdb.dbo.sysjobs
1027WHERE (name = @JobName)
1028IF (@JobID IS NOT NULL)
1029BEGIN
1030-- Check if the job is a multi-server job
1031IF (EXISTS (SELECT *
1032FROM msdb.dbo.sysjobservers
1033WHERE (job_id = @JobID) AND (server_id <> 0)))
1034BEGIN
1035-- There is, so abort the script
1036RAISERROR (N''''Unable to import job ''''''''AMS - Job Scheduler - Schedule Alerts'''''''' since there is already a multi-server job with this name.'''', 16, 1)
1037GOTO QuitWithRollback
1038END
1039ELSE
1040-- Delete the [local] job
1041EXECUTE msdb.dbo.sp_delete_job @job_name = @JobName
1042SELECT @JobID = NULL
1043END
1044
1045BEGIN
1046
1047-- Add the job
1048EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = @JobName, @owner_login_name = @JobOwnerLoginName, @description = @JobDescription, @category_name = @JobCategoryName, @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
1049IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1050
1051-- Add the job steps
1052EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = ''''Step1'''', @command = @CommandToExecute, @database_name = @DatabaseName, @server = N'''''''', @database_user_name = N'''''''', @subsystem = N''''TSQL'''', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'''''''', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
1053IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1054EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
1055
1056IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1057
1058-- Add the job schedules
1059EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N''''schedule'''', @enabled = 1, @freq_type = @freq_type, @active_start_date = null, @active_start_time = @TimeOfDayToRunSproc, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = @freq_relative_interval, @freq_recurrence_factor = @freq_recurrence_factor, @active_end_date = 99991231, @active_end_time = 235959
1060IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1061
1062-- Add the Target Servers
1063EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = @ServerName
1064IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
1065
1066END
1067COMMIT TRANSACTION
1068GOTO EndSave
1069QuitWithRollback:
1070IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
1071EndSave:
1072
1073
1074return @ErrorNo 
1075'' 
1076End
1077' 
1078,'^^^', getdate(), getdate())
1079GO
1080
1081---------------------------------------------------------------------------------------------------
1082-- Creation of proc 1 of 4 used in db maintenance (reindex, checkdb)
1083-- Proc also located in $/Spider/1.5/Database/SQLServerConfiguration/
1084---------------------------------------------------------------------------------------------------
1085insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
1086values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'DBMaintenanceProc', 1, 'Admin', 'Used for DB maintenance - proc 1 of 4'
1087,
1088'
1089create proc sp_AMSForEach_WorkerDB
1090--************************************************************************
1091--* Type: StoredProc (Public)
1092--* Name: sp_AMSForEach_WorkerDB
1093--* Author: Cameron Bellamy
1094--* Desc: Worker proc for sp_AMSForEachDB
1095--* Leveraged code from: sp_MSforeach_worker
1096--*
1097--*
1098--* %*% TAKE NOTE %*%
1099--* If you modify this proc definitinon you need to also modify the definition in:
1100--* $/Spider/1.5/SiteDB/Globals/Procs/sps_SiteDBConfigureInHouseServer.sqp
1101--* $/Spider/1.5/SiteDB/ServerCluster/Procs/spu_AMSSC.sqp
1102--* $/Spider/1.5/SiteDB/ProductVersion/AMS_ProductVersion.sqi
1103--*
1104--*
1105--* Parameters Description
1106--* ----------------------------------------------------------------------
1107--* command1 Command to execute
1108--*
1109--*
1110--***********************************************************************
1111/*
1112* This is the worker proc for all of the "for each" type procs. Its function is to read the
1113* next replacement name from the cursor (which returns only a single name), plug it into the
1114* replacement locations for the commands, and execute them. It assumes the cursor "hCForEachDB"
1115* has already been opened by its caller.
1116*/
1117
1118@command1 nvarchar(2000), @replacechar nchar(1) = N''?'', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null
1119as
1120
1121create table #qtemp ( /* Temp command storage */
1122qnum int NOT NULL,
1123qchar nvarchar(2000) COLLATE database_default NULL
1124)
1125
1126set nocount on
1127declare @name nvarchar(517), @namelen int, @q1 nvarchar(2000), @q2 nvarchar(2000)
1128declare @q3 nvarchar(2000), @q4 nvarchar(2000), @q5 nvarchar(2000)
1129declare @q6 nvarchar(2000), @q7 nvarchar(2000), @q8 nvarchar(2000), @q9 nvarchar(2000), @q10 nvarchar(2000)
1130declare @cmd nvarchar(2000), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(2000)
1131declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)
1132
1133open hCForEachDB
1134fetch hCForEachDB into @name
1135
1136/* Loop for each database */
1137while (@@fetch_status >= 0) begin
1138/* Initialize. */
1139
1140/* save the original dbname */
1141select @namesave = @name
1142select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)
1143while (@cmd is not null) begin /* Generate @q* for exec() */
1144/*
1145* Parse each @commandX into a single executable batch.
1146* Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we''ll need to allow overflow.
1147* We also may append @commandX''s (signified by ''++'' as first letters of next @command).
1148*/
1149select @replacecharindex = charindex(@replacechar, @cmd)
1150while (@replacecharindex <> 0) begin
1151
1152/* 7.0, if name contains '' character, and the name has been single quoted in command, double all of them in dbname */
1153/* if the name has not been single quoted in command, do not doulbe them */
1154/* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */
1155select @name = @namesave
1156select @namelen = datalength(@name)
1157declare @tempindex int
1158if (substring(@cmd, @replacecharindex - 1, 1) = N'''''''') begin
1159/* if ? is inside of '''', we need to double all the '' in name */
1160select @name = REPLACE(@name, N'''''''', N'''''''''''')
1161end else if (substring(@cmd, @replacecharindex - 1, 1) = N''['') begin
1162/* if ? is inside of [], we need to double all the ] in name */
1163select @name = REPLACE(@name, N'']'', N'']]'')
1164end else if ((@name LIKE N''%].%]'') and (substring(@name, 1, 1) = N''['')) begin
1165/* ? is NOT inside of [] nor '''', and the name is in [owner].[name] format, handle it */
1166/* !!! work around, when using LIKE to find string pattern, can''t use ''['', since LIKE operator is treating ''['' as a wide char */
1167select @tempindex = charindex(N''].['', @name)
1168select @nametmp = substring(@name, 2, @tempindex-2 )
1169select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )
1170select @nametmp = REPLACE(@nametmp, N'']'', N'']]'')
1171select @nametmp2 = REPLACE(@nametmp2, N'']'', N'']]'')
1172select @name = N''['' + @nametmp + N''].['' + @nametmp2 + '']''
1173end else if ((@name LIKE N''%]'') and (substring(@name, 1, 1) = N''['')) begin
1174/* ? is NOT inside of [] nor '''', and the name is in [name] format, handle it */
1175/* j.i.c., since we should not fall into this case */
1176/* !!! work around, when using LIKE to find string pattern, can''t use ''['', since LIKE operator is treating ''['' as a wide char */
1177select @nametmp = substring(@name, 2, len(@name)-2 )
1178select @nametmp = REPLACE(@nametmp, N'']'', N'']]'')
1179select @name = N''['' + @nametmp + N'']''
1180end
1181/* Get the new length */
1182select @namelen = datalength(@name)
1183
1184/* start normal process */
1185if (datalength(@cmd) + @namelen - 1 > 2000) begin
1186/* Overflow; put preceding stuff into the temp table */
1187if (@useq > 9) begin
1188raiserror 55555 N''sp_MSforeach_worker assert failed: command too long''
1189close hCForEachDB
1190deallocate hCForEachDB
1191return 1
1192end
1193if (@replacecharindex < @namelen) begin
1194/* If this happened close to beginning, make sure expansion has enough room. */
1195/* In this case no trailing space can occur as the row ends with @name. */
1196select @nextcmd = substring(@cmd, 1, @replacecharindex)
1197select @cmd = substring(@cmd, @replacecharindex + 1, 2000)
1198select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)
1199select @replacecharindex = charindex(@replacechar, @cmd)
1200insert #qtemp values (@useq, @nextcmd)
1201select @useq = @useq + 1
1202continue
1203end
1204/* Move the string down and stuff() in-place. */
1205/* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */
1206/* In this case, the char to be replaced is moved over by one. */
1207insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))
1208if (substring(@cmd, @replacecharindex - 1, 1) = N'' '') begin
1209select @cmd = N'' '' + substring(@cmd, @replacecharindex, 2000)
1210select @replacecharindex = 2
1211end else begin
1212select @cmd = substring(@cmd, @replacecharindex, 2000)
1213select @replacecharindex = 1
1214end
1215select @useq = @useq + 1
1216end
1217select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
1218select @replacecharindex = charindex(@replacechar, @cmd)
1219end
1220
1221/* Done replacing for current @cmd. Get the next one and see if it''s to be appended. */
1222select @usecmd = @usecmd + 1
1223select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end
1224if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N''++'') begin
1225insert #qtemp values (@useq, @cmd)
1226select @cmd = substring(@nextcmd, 3, 2000), @useq = @useq + 1
1227continue
1228end
1229
1230/* Now exec() the generated @q*, and see if we had more commands to exec(). Continue even if errors. */
1231/* Null them first as the no-result-set case won''t. */
1232select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null
1233select @q1 = qchar from #qtemp where qnum = 1
1234select @q2 = qchar from #qtemp where qnum = 2
1235select @q3 = qchar from #qtemp where qnum = 3
1236select @q4 = qchar from #qtemp where qnum = 4
1237select @q5 = qchar from #qtemp where qnum = 5
1238select @q6 = qchar from #qtemp where qnum = 6
1239select @q7 = qchar from #qtemp where qnum = 7
1240select @q8 = qchar from #qtemp where qnum = 8
1241select @q9 = qchar from #qtemp where qnum = 9
1242select @q10 = qchar from #qtemp where qnum = 10
1243truncate table #qtemp
1244exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)
1245select @cmd = @nextcmd, @useq = 1
1246end /* while @cmd is not null, generating @q* for exec() */
1247
1248/* All commands done for this name. Go to next one. */
1249fetch hCForEachDB into @name
1250end /* while FETCH_SUCCESS */
1251close hCForEachDB
1252deallocate hCForEachDB
1253return 0
1254'
1255,'^^^', getdate(), getdate())
1256GO
1257
1258---------------------------------------------------------------------------------------------------
1259-- Creation of proc 2 of 4 used in db maintenance (reindex, checkdb)
1260-- Proc also located in $/Spider/1.5/Database/SQLServerConfiguration/
1261---------------------------------------------------------------------------------------------------
1262insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
1263values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'DBMaintenanceProc', 2, 'Admin', 'Used for DB maintenance - proc 2 of 4'
1264,
1265'
1266create proc sp_AMSForEach_WorkerTable
1267--************************************************************************
1268--* Type: StoredProc (Public)
1269--* Name: sp_AMSForEach_WorkerTable
1270--* Author: Cameron Bellamy
1271--* Desc: Worker proc for sp_AMSForEachTable
1272--* Leveraged code from: sp_MSforeach_worker
1273--*
1274--*
1275--* %*% TAKE NOTE %*%
1276--* If you modify this proc definitinon you need to also modify the definition in:
1277--* $/Spider/1.5/SiteDB/Globals/Procs/sps_SiteDBConfigureInHouseServer.sqp
1278--* $/Spider/1.5/SiteDB/ServerCluster/Procs/spu_AMSSC.sqp
1279--* $/Spider/1.5/SiteDB/ProductVersion/AMS_ProductVersion.sqi
1280--*
1281--*
1282--* Parameters Description
1283--* ----------------------------------------------------------------------
1284--* command1 Command to execute
1285--*
1286--*
1287--***********************************************************************
1288/*
1289* This is the worker proc for all of the "for each" type procs. Its function is to read the
1290* next replacement name from the cursor (which returns only a single name), plug it into the
1291* replacement locations for the commands, and execute them. It assumes the cursor "hCForEachTable"
1292* has already been opened by its caller.
1293*/
1294
1295@command1 nvarchar(2000), @replacechar nchar(1) = N''?'', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null
1296as
1297
1298create table #qtemp ( /* Temp command storage */
1299qnum int NOT NULL,
1300qchar nvarchar(2000) COLLATE database_default NULL
1301)
1302
1303set nocount on
1304declare @name nvarchar(517), @namelen int, @q1 nvarchar(2000), @q2 nvarchar(2000)
1305declare @q3 nvarchar(2000), @q4 nvarchar(2000), @q5 nvarchar(2000)
1306declare @q6 nvarchar(2000), @q7 nvarchar(2000), @q8 nvarchar(2000), @q9 nvarchar(2000), @q10 nvarchar(2000)
1307declare @cmd nvarchar(2000), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(2000)
1308declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)
1309
1310open hCForEachTable
1311fetch hCForEachTable into @name
1312
1313/* Loop for each database */
1314while (@@fetch_status >= 0) begin
1315/* Initialize. */
1316
1317/* save the original dbname */
1318select @namesave = @name
1319select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)
1320while (@cmd is not null) begin /* Generate @q* for exec() */
1321/*
1322* Parse each @commandX into a single executable batch.
1323* Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we''ll need to allow overflow.
1324* We also may append @commandX''s (signified by ''++'' as first letters of next @command).
1325*/
1326select @replacecharindex = charindex(@replacechar, @cmd)
1327while (@replacecharindex <> 0) begin
1328
1329/* 7.0, if name contains '' character, and the name has been single quoted in command, double all of them in dbname */
1330/* if the name has not been single quoted in command, do not doulbe them */
1331/* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */
1332select @name = @namesave
1333select @namelen = datalength(@name)
1334declare @tempindex int
1335if (substring(@cmd, @replacecharindex - 1, 1) = N'''''''') begin
1336/* if ? is inside of '''', we need to double all the '' in name */
1337select @name = REPLACE(@name, N'''''''', N'''''''''''')
1338end else if (substring(@cmd, @replacecharindex - 1, 1) = N''['') begin
1339/* if ? is inside of [], we need to double all the ] in name */
1340select @name = REPLACE(@name, N'']'', N'']]'')
1341end else if ((@name LIKE N''%].%]'') and (substring(@name, 1, 1) = N''['')) begin
1342/* ? is NOT inside of [] nor '''', and the name is in [owner].[name] format, handle it */
1343/* !!! work around, when using LIKE to find string pattern, can''t use ''['', since LIKE operator is treating ''['' as a wide char */
1344select @tempindex = charindex(N''].['', @name)
1345select @nametmp = substring(@name, 2, @tempindex-2 )
1346select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )
1347select @nametmp = REPLACE(@nametmp, N'']'', N'']]'')
1348select @nametmp2 = REPLACE(@nametmp2, N'']'', N'']]'')
1349select @name = N''['' + @nametmp + N''].['' + @nametmp2 + '']''
1350end else if ((@name LIKE N''%]'') and (substring(@name, 1, 1) = N''['')) begin
1351/* ? is NOT inside of [] nor '''', and the name is in [name] format, handle it */
1352/* j.i.c., since we should not fall into this case */
1353/* !!! work around, when using LIKE to find string pattern, can''t use ''['', since LIKE operator is treating ''['' as a wide char */
1354select @nametmp = substring(@name, 2, len(@name)-2 )
1355select @nametmp = REPLACE(@nametmp, N'']'', N'']]'')
1356select @name = N''['' + @nametmp + N'']''
1357end
1358/* Get the new length */
1359select @namelen = datalength(@name)
1360
1361/* start normal process */
1362if (datalength(@cmd) + @namelen - 1 > 2000) begin
1363/* Overflow; put preceding stuff into the temp table */
1364if (@useq > 9) begin
1365raiserror 55555 N''sp_MSforeach_worker assert failed: command too long''
1366close hCForEachTable
1367deallocate hCForEachTable
1368return 1
1369end
1370if (@replacecharindex < @namelen) begin
1371/* If this happened close to beginning, make sure expansion has enough room. */
1372/* In this case no trailing space can occur as the row ends with @name. */
1373select @nextcmd = substring(@cmd, 1, @replacecharindex)
1374select @cmd = substring(@cmd, @replacecharindex + 1, 2000)
1375select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)
1376select @replacecharindex = charindex(@replacechar, @cmd)
1377insert #qtemp values (@useq, @nextcmd)
1378select @useq = @useq + 1
1379continue
1380end
1381/* Move the string down and stuff() in-place. */
1382/* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */
1383/* In this case, the char to be replaced is moved over by one. */
1384insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))
1385if (substring(@cmd, @replacecharindex - 1, 1) = N'' '') begin
1386select @cmd = N'' '' + substring(@cmd, @replacecharindex, 2000)
1387select @replacecharindex = 2
1388end else begin
1389select @cmd = substring(@cmd, @replacecharindex, 2000)
1390select @replacecharindex = 1
1391end
1392select @useq = @useq + 1
1393end
1394select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
1395select @replacecharindex = charindex(@replacechar, @cmd)
1396end
1397
1398/* Done replacing for current @cmd. Get the next one and see if it''s to be appended. */
1399select @usecmd = @usecmd + 1
1400select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end
1401if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N''++'') begin
1402insert #qtemp values (@useq, @cmd)
1403select @cmd = substring(@nextcmd, 3, 2000), @useq = @useq + 1
1404continue
1405end
1406
1407/* Now exec() the generated @q*, and see if we had more commands to exec(). Continue even if errors. */
1408/* Null them first as the no-result-set case won''t. */
1409select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null
1410select @q1 = qchar from #qtemp where qnum = 1
1411select @q2 = qchar from #qtemp where qnum = 2
1412select @q3 = qchar from #qtemp where qnum = 3
1413select @q4 = qchar from #qtemp where qnum = 4
1414select @q5 = qchar from #qtemp where qnum = 5
1415select @q6 = qchar from #qtemp where qnum = 6
1416select @q7 = qchar from #qtemp where qnum = 7
1417select @q8 = qchar from #qtemp where qnum = 8
1418select @q9 = qchar from #qtemp where qnum = 9
1419select @q10 = qchar from #qtemp where qnum = 10
1420truncate table #qtemp
1421exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)
1422select @cmd = @nextcmd, @useq = 1
1423end /* while @cmd is not null, generating @q* for exec() */
1424
1425/* All commands done for this name. Go to next one. */
1426fetch hCForEachTable into @name
1427end /* while FETCH_SUCCESS */
1428close hCForEachTable
1429deallocate hCForEachTable
1430return 0
1431'
1432,'^^^', getdate(), getdate())
1433GO
1434
1435---------------------------------------------------------------------------------------------------
1436-- Creation of proc 3 of 4 used in db maintenance (reindex, checkdb)
1437-- Proc also located in $/Spider/1.5/Database/SQLServerConfiguration/
1438---------------------------------------------------------------------------------------------------
1439insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
1440values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'DBMaintenanceProc', 3, 'Admin', 'Used for DB maintenance - proc 3 of 4'
1441,
1442'
1443create proc sp_AMSForEachDB
1444--************************************************************************
1445--* Type: StoredProc (Public)
1446--* Name: sp_AMSForEachDB
1447--* Author: Cameron Bellamy
1448--* Desc: Executes a command against all user databases on server
1449--* Leveraged code from: sp_Msforeachdb
1450--*
1451--*
1452--* %*% TAKE NOTE %*%
1453--* If you modify this proc definitinon you need to also modify the definition in:
1454--* $/Spider/1.5/SiteDB/Globals/Procs/sps_SiteDBConfigureInHouseServer.sqp
1455--* $/Spider/1.5/SiteDB/ServerCluster/Procs/spu_AMSSC.sqp
1456--* $/Spider/1.5/SiteDB/ProductVersion/AMS_ProductVersion.sqi
1457--*
1458--*
1459--* Parameters Description
1460--* ----------------------------------------------------------------------
1461--* command1 Command to execute
1462--*
1463--*
1464--***********************************************************************
1465
1466@command1 nvarchar(2000), @replacechar nchar(1) = N''?'', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
1467@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
1468as
1469set deadlock_priority low
1470
1471/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
1472/* @precommand and @postcommand may be used to force a single result set via a temp table. */
1473
1474/* Preprocessor won''t replace within quotes so have to use str(). */
1475declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
1476select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
1477select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
1478select @dbinaccessible = N''0x80000000'' /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn''t work in convert() */
1479
1480if (@precommand is not null)
1481exec(@precommand)
1482
1483declare @origdb nvarchar(128)
1484select @origdb = db_name()
1485
1486/* If it''s a single user db and there''s an entry for it in sysprocesses who isn''t us, we can''t use it. */
1487/* Create the select */
1488exec(N''declare hCForEachDB cursor global for select name from master.dbo.sysdatabases d '' +
1489N'' where d.name not in (''''master'''', ''''model'''', ''''tempdb'''', ''''msdb'''')'' +
1490N'' and (d.status & '' + @inaccessible + N'' = 0)'' +
1491N'' and (DATABASEPROPERTY(d.name, ''''isdboonly'''') = 0 and (has_dbaccess(d.name) = 1))'' +
1492N'' and ((DATABASEPROPERTY(d.name, ''''issingleuser'''') = 0 and (has_dbaccess(d.name) = 1)) or '' +
1493N'' ( DATABASEPROPERTY(d.name, ''''issingleuser'''') = 1 and not exists '' +
1494N'' (select * from master.dbo.sysprocesses p where dbid = d.dbid and p.spid <> @@spid)))'' )
1495
1496declare @retval int
1497select @retval = @@error
1498if (@retval = 0)
1499exec @retval = sp_AMSForEach_WorkerDB @command1, @replacechar, @command2, @command3
1500
1501if (@retval = 0 and @postcommand is not null)
1502exec(@postcommand)
1503
1504declare @tempdb nvarchar(258)
1505SELECT @tempdb = REPLACE(@origdb, N'']'', N'']]'')
1506exec (N''use '' + N''['' + @tempdb + N'']'')
1507
1508return @retval
1509'
1510,'^^^', getdate(), getdate())
1511GO
1512
1513---------------------------------------------------------------------------------------------------
1514-- Creation of proc 4 of 4 used in db maintenance (reindex, checkdb)
1515-- Proc also located in $/Spider/1.5/Database/SQLServerConfiguration/
1516---------------------------------------------------------------------------------------------------
1517insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
1518values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'DBMaintenanceProc', 4, 'Admin', 'Used for DB maintenance - proc 4 of 4'
1519,
1520'
1521create proc sp_AMSForEachTable
1522--************************************************************************
1523--* Type: StoredProc (Public)
1524--* Name: sp_AMSForEachTable
1525--* Author: Cameron Bellamy
1526--* Desc: Executes a command against all tables in a database
1527--* Leveraged code from: sp_Msforeachtable
1528--*
1529--*
1530--* %*% TAKE NOTE %*%
1531--* If you modify this proc definitinon you need to also modify the definition in:
1532--* $/Spider/1.5/SiteDB/Globals/Procs/sps_SiteDBConfigureInHouseServer.sqp
1533--* $/Spider/1.5/SiteDB/ServerCluster/Procs/spu_AMSSC.sqp
1534--* $/Spider/1.5/SiteDB/ProductVersion/AMS_ProductVersion.sqi
1535--*
1536--*
1537--* Parameters Description
1538--* ----------------------------------------------------------------------
1539--* command1 Command to execute
1540--*
1541--*
1542--***********************************************************************
1543
1544@command1 nvarchar(2000), @replacechar nchar(1) = N''?'', @command2 nvarchar(2000) = null,
1545@command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
1546@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
1547as
1548/* This proc returns one or more rows for each table (optionally, matching @where), with each table defaulting to its own result set */
1549/* @precommand and @postcommand may be used to force a single result set via a temp table. */
1550
1551/* Preprocessor won''t replace within quotes so have to use str(). */
1552declare @mscat nvarchar(12)
1553select @mscat = ltrim(str(convert(int, 0x0002)))
1554
1555if (@precommand is not null)
1556exec(@precommand)
1557
1558/* Create the select */
1559exec(N''declare hCForEachTable cursor global for select ''''['''' + REPLACE(user_name(uid), N'''']'''', N'''']]'''') + '''']'''' + ''''.'''' + ''''['''' + REPLACE(object_name(id), N'''']'''', N'''']]'''') + '''']'''' from dbo.sysobjects o ''
1560+ N'' where OBJECTPROPERTY(o.id, N''''IsUserTable'''') = 1 '' + N'' and o.category & '' + @mscat + N'' = 0 ''
1561+ @whereand)
1562declare @retval int
1563select @retval = @@error
1564if (@retval = 0)
1565exec @retval = sp_AMSForEach_WorkerTable @command1, @replacechar, @command2, @command3
1566
1567if (@retval = 0 and @postcommand is not null)
1568exec(@postcommand)
1569
1570return @retval
1571'
1572,'^^^', getdate(), getdate())
1573GO
1574
1575insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
1576values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'JobCreationDropProc', 1, 'Admin', 'Drops procedure for job creation'
1577,
1578'
1579if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[sp_AMSCreateAgentJob]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
1580drop procedure [dbo].[sp_AMSCreateAgentJob]
1581'
1582,'^^^', getdate(), getdate())
1583GO
1584
1585insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
1586values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'DBMaintenanceDropProc', 1, 'Admin', 'Used to drop the DB maintenance proc - proc 1 of 4'
1587,
1588'
1589if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[sp_AMSForEach_WorkerDB]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
1590drop procedure [dbo].[sp_AMSForEach_WorkerDB]
1591'
1592,'^^^', getdate(), getdate())
1593GO
1594
1595insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
1596values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'DBMaintenanceDropProc', 2, 'Admin', 'Used to drop the DB maintenance proc - proc 2 of 4'
1597,
1598'
1599if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[sp_AMSForEach_WorkerTable]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
1600drop procedure [dbo].[sp_AMSForEach_WorkerTable]
1601'
1602,'^^^', getdate(), getdate())
1603GO
1604
1605insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
1606values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'DBMaintenanceDropProc', 3, 'Admin', 'Used to drop the DB maintenance proc - proc 3 of 4'
1607,
1608'
1609if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[sp_AMSForEachDB]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
1610drop procedure [dbo].[sp_AMSForEachDB]
1611'
1612,'^^^', getdate(), getdate())
1613GO
1614
1615insert into AMS_ProductVersionScript (AMSProdId, AMSProdVersion, AMSScriptGroup, AMSScriptStepNo, AMSScriptType, AMSScriptDescr, AMSScriptText, ChangedBy, ChangedDate, EnteredDate)
1616values (dbo.fnc_GetConstant('PRODUCTID_AMS360'), '1.0.1281.1', 'DBMaintenanceDropProc', 4, 'Admin', 'Used to drop the DB maintenance proc - proc 4 of 4'
1617,
1618'
1619if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[sp_AMSForEachTable]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
1620drop procedure [dbo].[sp_AMSForEachTable]
1621'
1622,'^^^', getdate(), getdate())
1623GO
1624
1625if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spa_ProductVersionScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
1626exec [dbo].[spa_ProductVersionScript] 'A'
1627GO
1628
1629
1630
1631--AMS_PRODUCTVERSIONSCRIPT.SQI content is included here; it is long----------------------<
1632--AMS_PRODUCTVERSIONSCRIPT.SQI content is included here; it is long----------------------<
1633--AMS_PRODUCTVERSIONSCRIPT.SQI content is included here; it is long----------------------<
1634--AMS_PRODUCTVERSIONSCRIPT.SQI content is included here; it is long----------------------<
1635
1636
1637
1638
1639
1640use master
1641
1642-- Declare and initialize variables
1643declare @sql varchar(max)
1644,@iRowcount int
1645,@iError int
1646
1647select @sql = null
1648,@iRowcount = 0
1649,@iError = 0
1650
1651select @sql = AMSScriptText
1652from SiteDB.dbo.AMS_ProductVersionScript 
1653where AMSProdId = '70DD0AE7-F309-4473-98B1-A47F144F2C46' 
1654and AMSScriptStepNo = 100
1655and AMSScriptGroup = 'Security'
1656
1657if @sql is not null
1658begin
1659select 'Start: SiteDB logins'
1660use master
1661exec(@sql)
1662
1663select @iRowcount= @@rowcount, @iError = @@error
1664select @sql = null
1665if @iError = 0
1666begin
1667select 'End: SiteDB logins'
1668end
1669else
1670begin
1671select 'Error: creating SiteDB logins'
1672end
1673end
1674else
1675begin
1676select 'Error: SiteDB logins not found in AMS_ProductVersionScript'
1677end
1678
1679------------------------------------------------------------------
1680--
1681--Create custom 360 error messages. Same as sysmessages.sqd
1682--
1683------------------------------------------------------------------
1684use master
1685select @sql = AMSScriptText
1686from SiteDB.dbo.AMS_ProductVersionScript 
1687where AMSProdId = '0B984CB5-54D4-45F9-A4D1-730F5673E0B8' 
1688and AMSScriptStepNo = 1
1689and AMSScriptGroup = 'Sysmessage'
1690
1691if @sql is not null
1692begin
1693select 'Start: create 360 error messages'
1694use master
1695exec(@sql)
1696
1697select @iRowcount= @@rowcount, @iError = @@error
1698select @sql = null
1699if @iError = 0
1700begin
1701select 'End: create 360 error messages'
1702end
1703else
1704begin
1705select 'Error: create 360 error messages'
1706end
1707end
1708else
1709begin
1710select 'Error: Sysmessage not found in AMS_ProductVersionScript'
1711end
1712
1713------------------------------------------------------------------
1714--
1715--server settings
1716--
1717------------------------------------------------------------------
1718use master
1719select @sql = AMSScriptText
1720from SiteDB.dbo.AMS_ProductVersionScript 
1721where AMSProdId = '0B984CB5-54D4-45F9-A4D1-730F5673E0B8' 
1722and AMSScriptStepNo = 1
1723and AMSScriptGroup = 'ServerSettings'
1724
1725if @sql is not null
1726begin
1727select 'Start: apply database server setting'
1728use master
1729exec(@sql)
1730
1731select @iRowcount= @@rowcount, @iError = @@error
1732select @sql = null
1733if @iError = 0
1734begin
1735select 'End: apply database server setting'
1736end
1737else
1738begin
1739select 'Error: applying database server setting'
1740end
1741end
1742else
1743begin
1744select 'Error: ServerSettings not found in AMS_ProductVersionScript'
1745end
1746
1747------------------------------------------------------------------
1748--
1749--Create users and apply roles for SiteDB. Same as SiteDBRoles.sqr 
1750--
1751------------------------------------------------------------------
1752use SiteDB
1753select @sql = AMSScriptText
1754from SiteDB.dbo.AMS_ProductVersionScript 
1755where AMSProdId = '70DD0AE7-F309-4473-98B1-A47F144F2C46' 
1756and AMSScriptStepNo = 200
1757and AMSScriptGroup = 'Security'
1758
1759if @sql is not null
1760begin
1761select 'Start: SiteDB users and applying roles'
1762use SiteDB
1763exec(@sql)
1764
1765select @iRowcount= @@rowcount, @iError = @@error
1766select @sql = null
1767if @iError = 0
1768begin
1769select 'End: SiteDB users and applying roles'
1770end
1771else
1772begin
1773select 'Error: create SiteDB users and roles'
1774end
1775end
1776else
1777begin
1778select 'Error: SiteDB Security not found in AMS_ProductVersionScript'
1779end
1780
1781
1782------------------------------------------------------------------
1783--
1784--Create users and apply roles for all agency databases. Same as AMS360DBRoles.sqr 
1785--Also, set compatibility level to 100 (Sql2008)
1786--
1787------------------------------------------------------------------
1788--------------------------------------------------------------
1789-- Get a handle of all databases on this server that are not 
1790-- in the middle of a restore
1791--------------------------------------------------------------
1792use SiteDB
1793declare @Name varchar(100)
1794declare db cursor
1795for
1796select
1797AMSAgenDBName
1798from 
1799SiteDb.dbo.AMS_CustomerAgency
1800where 
1801AMSAgenDBStatus in ('O','F')
1802and 
1803AMSAgenDBName in (select name
1804from master.dbo.sysdatabases
1805where status & 32 <> 32)
1806
1807open db
1808fetch next from db into @Name
1809
1810--------------------------------------------------------------
1811-- Loop over each agency database:
1812-- 1) Change compatability level to 100
1813-- 2) Create users and roles
1814--------------------------------------------------------------
1815while @@fetch_status = 0
1816begin
1817select 'Start: create users and applying roles to agency database: ' + @name
1818exec sp_dbcmptlevel @Name, 100
1819
1820select @SQL = 'use [' + @Name + ']' + char(13)
1821select @SQL = @SQL + char(13) + convert(varchar(max),AMSScriptText) + char(13)
1822from AMS_ProductVersionScript 
1823where AMSProdId = '0B984CB5-54D4-45F9-A4D1-730F5673E0B8'
1824and AMSScriptStepNo = 200
1825and AMSScriptGroup = 'Security'
1826
1827exec(@SQL)
1828
1829select @iRowcount= @@rowcount, @iError = @@error
1830select @sql = null
1831if @iError = 0
1832begin
1833select 'End: create users and applying roles to agency database: ' + @name
1834end
1835else
1836begin
1837select 'Error: create users and roles to agency database: ' + @name
1838end
1839
1840fetch next from db into @Name
1841end
1842
1843close db
1844deallocate db
1845
1846print '########SiteDB and other agency DBs done#######'
1847print ''; print ''; print ''; print '';