Controlling Access to Stored Procedures, Triggers, and Views
Teratrax Source Guard is natively integrated with Microsoft Visual
SourceSafe. The Server Explorer provides full support for team collaboration
and group development by allowing you to check out database objects in order
to work with them. Once you're done with your changes, check in your stored
procedure, trigger, or view back to Microsoft Visual SourceSafe to allow
other users to modify them. During the time the object is checked out,
Teratrax Source Guard prevents all other users from editing or modifying the
database object, however, users of other database tools and editors (for
example, Query Analyzer) can still modify database objects (if they have
privileges in the database) and bypass Microsoft Visual SourceSafe causing
the SQL Server database to be out of sync with the Visual SourceSafe
database.
To ensure that all object modification is recorded in Visual SourceSafe,
you need to restrict stored procedure, trigger, and view modifications to
Teratrax Source Guard only. The following steps allow you to achieve this:
1. Create a SQL Server login for Teratrax Source Guard with a private
password (this example uses SQL Server Authentication):
EXECUTE SP_ADDLOGIN 'TSGLogin', 'private_password'
2. Give the new login db_owner access to the database you intend to add
to Visual SourceSafe:
USE MyDatabase
EXECUTE SP_GRANTDBACCESS 'TSGLogin', 'TSGUser'
EXECUTE SP_ADDROLEMEMBER 'db_owner', 'TSGUser'
3. Start Teratrax Source Guard and connect to SQL Server. In the
connection window, select SQL Server Authentication and type TSGLogin/private_password
in the Login Name/Password boxes respectively. Check the "Remember
connection parameters to this server" box and click Connect
4. Perform step 3 on all clients using Teratrax Source Guard
5. Determine the list of logins that you want restrict from modifying stored procedures, triggers, and views
6. Remove these logins from all server roles
7. Remove the users associated with these logins from the db_owner and
db_ddladmin database roles (You may want to add them to db_datareader and
db_datawriter so they can view and modify data. Also, you may want to grant
them permission to execute stored procedures if they need to)
Once the above steps are completed, restricted users can modify database
objects only with Teratrax Source Guard. This ensures that all stored
procedures, triggers, and views are checked in Visual SourceSafe before they
can be modified.
|
