Network Administrator - Adds and removes users on the network, manages permissions and sharing of network resources.
SourceSafe Administrator - Adds and removes users within SourceSafe, manages all permissions and security within SourceSafe. The SourceSafe Administrator is created with the installation of SourceSafe and is not assigned to an interactive user account. Access is granted to anyone with access to the share and knowledge of the password.
dbChangeControl Administrator - Owner of all "Database" sub-Projects in SourceSafe. Initializes all "Database" sub-Projects. Must have Add rights to the root project of the SourceSafe share. A SourceSafe password should always be assigned to the dbChangeControl Administrator. In smaller development shops, the SourceSafe Administrator and the dbChangeControl Administrator can be handled by the default SourceSafe Admin account (with a password added of course). In larger organizations, a more studied approach is likely. Like the SourceSafe Administrator, the dbChangeControl Administrator is not assigned to an interactive user account. Access is granted to anyone with access to the share and knowledge of the password. Unlike the SourceSafe Administrator, the dbChangeControl Administrator cannot assign rights or set permissions within source. Destroy rights may or may not be granted to the dbChangeControl Administrator. This too is determined by the size and philosophy of the organization.
Database Developer - Owner of first level in environment hierarchy - unit test. Every developer is a partner in ownership of the second level of environment hierarchy - system test. Can subscribe to any project node where read access has been granted in SourceSafe. Can promote any project from first level in environment hierarchy to second level in environment hierarchy. Can play all Configuration stories that do not require dbChangeControl Administrator SourceSafe permissions and Usability Stories in the first and second levels of the environment hierarchy. A dbChangeControl Database Developer cannot initiate a project because that requires SourceSafe rights The developer maintains unfettered access to the local dbChangeControl metadata and file system for the "Unit test". Database developers will have full capabilities to check-in, check-out, and add objects to SourceSafe to the unit and system test environments.
QA Administrator - Can promote any project from the Development environment to the QA environment. Can perform Usability Stories in the QA environment.
Production Administrator - Can promote any project from the QA environment to the Production environment. Can perform Usability Stories in the Production environment.
Add system messages: Create a central mechanism - a single point of maintenance - for adding error messages to the system. dbChangeControl uses messages in the range 59000-59099. Keep a stored procedure that drops each message in if it already exists, then adds that message error number as defined in the stored procedure. If any messages exist in sysmessages in the used range they may be be lost.
exec admin.dbo.adminPopulateSysmessages
Initialize a SourceSafe project Hierarchy for dbChangeControl - In SourceSafe, create a new project for the specified project name and sub-projects for all environments. This can be done from the SourceSafe client GUI or alternately by using the stored procedure vssAdminInitProject. If you use the stored procedure provide the user name and password of the SourceSafe Administrator or the dbChangeControl Administrator. I hesitate to use this stored procedure only because if you pass a bad user name or password, the process will hang trying to send a message prompt through xp_cmdshell. You'll have to go to the task manager on the SQL Server console to kill the job (look for SS.EXE) else it will hang out there wasting resources. On the other hand, the stored procedure is a much more convenient method of creating the project paths used by dbChangeControl. Requires root level project add privileges and access to the SourceSafe Administrator tool. It is recommended that only the SourceSafe Administrator have this ability. Once a project is added it is necessary for the SourceSafe Administrator to set proper permissions within SourceSafe.
admin.dbo.vssAdminInitProject 'MyNewProject',
'\\BILLSRV\MySourceSafe\',
'dbChangeControlAdmin',
'password'
From Visual SourceSafe Admin tool: Whether the project is created directly in SourceSafe or through the stored procedure, an additional step is required to grant adequate permissions in SourceSafe for Users
Tools/Rights by Project/Add/Rename/Delete rights to user’s environment.
In the screen shots, note that the first user, Administrator has Read rights only to the QA projects and has Add/Rename/Delete rights in Unit projects. This user has Add/Rename/Delete rights to System environments as well revealing that the user is developer. The second user has only Read rights everywhere, indicating that the use is a tourist or perhaps an application developer that can view any projects in the database hierarchy but cannot change the content. The third user has Add/Rename/Delete rights everywhere. This user is, as the name suggests, the dbChangeControl Administrator. Notice that no user has the destroy right meaning that only logical deletes are permitted where delete permission is granted. This will insure that all history is retained with SourceSafe.
Subscribe a Server to dbChangeControl - In unit test and system test a database developer owns the subscription. I QA or Production environments, the designated dbChangeControl Administrator owns the subscription. The subscription owner must be dbo in the nodes admin database, must have change access to the fie system location where scripts will be placed on the server, and must have Add rights to the sub project in SourceSafe as well as access to the SourceSafe Share.
There are numerous steps to Subscription, all of which are automated into the stored procedure adminAddNodeGrande
Script Server to File System Hierarchy - From Query Analyze, SQL Agent Job Scheduler or the ASP front end, script Backup Devices, Logins, Alerts, Jobs, and Operators from the current SQL Server to the file system hierarchy. The hierarchy must be in place else the scripting request will fail. On workstations running an instance of SQL 7 and one or more instances of SQL 2000 This script must be run from a SQL 2000 instance only - the SQL 2000 OLE_DB client libraries used for DMO do not seem to be compatible with the SQL 7 engine.
Script Database to File System Hierarchy - From Query Analyze, SQL Agent Job Scheduler or the ASP front end, script Database Defaults, Full Text Catalog, Database Roles, Rules, Stored Procedures, User Defined Data Types, User Defined Functions on SQL 2000 instances, and Views. The hierarchy must be in place else the scripting request will fail. On workstations running an instance of SQL 7 and one or more instances of SQL 2000 This script as written must be run from a SQL 2000 instance only. The SQL 2000 client libraries used for DMO do not seem to be compatible with SQL 7.
Script Table to File System Hierarchy - From Query Analyze, SQL Agent Job Scheduler or the ASP front end, script Tables, Constraints, Indexes, and Triggers for all tables in a database. The hierarchy must be in place else the scripting request will fail. On workstations running an instance of SQL 7 and one or more instances of SQL 2000 This script as written must be run from a SQL 2000 instance only. The SQL 2000 client libraries used for DMO do not seem to be compatible with SQL 7.
Build Table from File System Hierarchy - Custom Task. Use osql to execute scripts. Remember, this is not application code, but a script dedicated to the creation or an iterative update to a server so hard coding sequence is often faster than trying to figure out some clever scheme for ordering script execution sequence.
Build all Tables from File System Hierarchy - Custom Task. Use osql to execute scripts. Remember, this is not application code, but a script dedicated to the creation or an iterative update to a server so hard coding sequence is valid and often faster than trying to figure out and implement a magic scheme for ordering script execution sequence.
Build Database from File System Hierarchy - Custom Task. Use osql to execute scripts. Remember, this is not application code, but a script dedicated to the creation or an iterative update to a server so hard coding sequence is often faster than trying to figure out some clever scheme for ordering script execution sequence.
Build Database from File System Hierarchy - Custom Task. Use osql to execute scripts. Remember, this is not application code, but a script dedicated to the creation or an iterative update to a server so hard coding sequence is often faster than trying to figure out some clever scheme for ordering script execution sequence.
Build Server from File System Hierarchy - Custom Task. Use osql to execute scripts. Remember, this is not application code, but a script dedicated to the creation or an iterative update to a server so hard coding sequence is often faster than trying to figure out some clever scheme for ordering script execution sequence.
add/modify/delete Server objects - (Backup devices, Logins, Alerts, Jobs and operators, Databases) Use SourceSafe to compare and sync the server to SourceSafe, check out the server, create edit and/or delete server objects in the unit test environment, script server objects, check in the server.
add/modify/delete Linked Servers - Custom Task
add/modify/delete Database objects - (Defaults, Full Text Catalogues, Database Roles, Rules, Stored Procedures, Tables - including Constraints, Indexes, and triggers, User Defined Functions, User Defined Datatypes, Users, and Views) Use SourceSafe to sync the database to SourceSafe, check out the database, create edit and/or delete database objects in the unit test environment, script database objects, check in the server. Use SourceSafe or the compare procedures to identify differences between the database and the file system, the file system and SourceSafe, or even the database and SourceSafe with no regard to what's in the file system.
add/modify/delete Database Conversions - Custom Task. Use the generated scripts as building blocks to create a single script that will apply the changes for the current iteration to a database. Always include a rollback script that will restore the database to it's original state.
add/modify/delete Server Conversions - Custom Task. Use the generated scripts and possibly even the database level conversion scripts as building blocks to create a single script that will apply the changes for the current iteration to a server. Always include a fully tested rollback script with each conversion script.
add/modify/delete Project Conversions - Custom Task. Use the generated scripts and possibly even the database or server level conversion scripts as building blocks to create a single script that will apply the changes for the current iteration to a project environment. Always include a fully tested rollback script with each conversion script.
add/modify/delete Database HotFixes - Custom Task. Use the generated scripts as building blocks to create a single script that will apply the changes for the critical change to a database. Always include a fully tested rollback script with each hotfix script.
add/modify/delete Server HotFixes - Custom Task. Use the generated scripts and possibly even the database level conversion scripts as building blocks to create a single script that will apply the changes for the critical change to a server. Always include a fully tested rollback script with each hotfix script.
add/modify/delete Project HotFixes - Custom Task. Use the generated scripts and possibly even the database or server level conversion scripts as building blocks to create a single script that will apply the changes for the critical change to a project environment. Always include a fully tested rollback script with each hotfix script.
compare file system scripts and SourceSafe - Use SourceSafe or the procedure vssCompareWorkToVss to do this comparison. The procedure is useful from SQL Servers that do not have a SourceSafe Client installed.
compare SourceSafe environments - Use the SourceSafe client to do this comparison.
compare SourceSafe and database - Use procedure vssCompareDatabaseToVss
compare file system scripts and database - Use Procedure vssCompareGrande. Combines the two procedures vssCompareWorkToVss and vssCompareDatabaseToVss This approach is used in preference over using SourceSafe space to store temporary database images.
Add project scripts to SourceSafe - Use SourceSafe to check in and add all objects in the project level of the file system hierarchy. Each project will have a folder for documentation, along with one for conversion and another for hot fix scripts that involve multiple servers within the project.
Add server scripts to SourceSafe - Use SourceSafe to check in and add all objects in the server level of the file system hierarchy. This will include scripts to create backup devices, alerts, operators, scheduled jobs, and logins along with any conversions or hot fixes that impact more than one database on a server. (These levels are populated using the dmoScriptServer stored procedure.
Add database scripts to SourceSafe - Use SourceSafe to check in and add all objects at the database level of the file system hierarchy. These levels of the file system are populated using the dmoScriptDatabase and dmoScriptTables stored procedures. .
Check out all scripts for a Project Environment - Use SourceSafe to checkout recursively. This case is executed rarely if at all and is unneeded for any existing automated task.
Check out all scripts for a SQL Server - Use SourceSafe to checkout recursively. This could be done at the instance level as well. This case is most useful when scripting server level objects as part of the necessary check-out, script, check-in sequence either through automation or as manual steps.
Check out all scripts for a SQL Database - Use SourceSafe to checkout recursively or use the Admin.dbo.vssCheckOut stored procedure for automated processes.
check in all scripts for a Project Environment - Use SourceSafe to check in recursively. This case is executed rarely if at all and is unneeded for any existing automated task. Be sure to also add any new objects and remove any obsolete objects.
check in all scripts for a SQL Server - Use SourceSafe to check in recursively. This case is executed rarely if at all and is unneeded for any existing automated task. Be sure to also add any new objects and remove any obsolete objects.
check in all scripts for a SQL Database - Use SourceSafe to check in recursively or use the Admin.dbo.vssCheckIn stored procedure for automated processes.
undo a check out of all scripts for a Project Environment - Use SourceSafe to undo all check outs recursively. This case is executed rarely if at all and is unneeded for any existing automated task. Be sure to also remove any new objects that may have been added to SourceSafe.
undo a check out of all scripts for server - Use SourceSafe to undo all check outs recursively. This case is executed rarely if at all and is unneeded for any existing automated task. Be sure to also remove any new objects that may have been added to SourceSafe.
undo a check out of all scripts for a database - Use SourceSafe to undo all check outs recursively. This case is executed rarely if at all and is unneeded for any existing automated task. Be sure to also remove any new objects that may have been added to SourceSafe.
get latest copy of all scripts for a server from SourceSafe - Use SourceSafe to get latest recursively for the server. This case is executed rarely if at all and is unneeded for any existing automated task.
get latest copy of all scripts for a database from SourceSafe - Use SourceSafe to get latest recursively for the database or use stored procedure Admin.dbo.vssGetLatest for automated processing requiring a copy of the latest SourceSafe objects in the local working folders.
label a project
Extract Data to File System Hierarchy - Custom Task. Use DTS or BCP. DTS packages or VB source in SQL 2000 can be used to store the DTS package.
Populate Table from File System Hierarchy - Custom Task. Use BULK LOAD scripts and/or DTS. DTS packages or VB source in SQL 2000 can be used to store the DTS package. An advantage for BCP is that the T-SQL BULK LOAD statement can be used to BCP the data in making unitizing of build scripts more straightforward.
Return to the dbChangeControl index page.