20
 |  |
When installing an enterprise application with Microsoft SQL Server (Microsoft SQL), it is important to be careful with the database user, SQL Authentication, and the default schema. The SQL scripts provided in this document would apply to any scenario where you need to change the owner of tables, views, or stored procedures in any Microsoft SQL database.
Note: For this article's reference, I reference Lombardi Teamworks (v7.0, v6.x and earlier), WebSphere Lombardi Edition (v7.1, 7.2), and IBM Business Process Manager (v7.5 and later).
When you install these products with Microsoft SQL Server as the database and choose SQL authentication, the default schema needs to be the same as the user name.
Example: The database user is 'teamworks', then all tables after installation will be similar to the following name: teamworks.LSW_tableName. If your tables read dbo.LSW_tableName then you will need to correct the schema name. When installing with Microsoft SQL Authentication, there are various built-in SQL queries, which look for dbUserName.LSW_table. You might see error messages that are similar to the following text in log files:
Error Example 1 - The table LSW_SYSTEM exists, but it cannot be reached because it is owned by dbo.
2011-06-10 15:18:52,593 [SIBJMSRAThreadPool : 7] ERROR com.lombardisoftware.server.ejb.persistence.PersistenceServicesBean -org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into LSW_SYSTEM (SYSTEM_ID,NAME,DESCRIPTION,ARCHIVED,VERSION,LAST_MODIFIED,LAST_MODIFIED_BY_USER_ID) values (?,?,?,?,?,?,?)]; SQL state []; error code [0]; Cannot insert duplicate key rowin object 'teamworks.LSW_SYSTEM'
Error Example 2 - The auto creation scripts for the performance server wants to create tables with the schema teamworks. In this case, the user teamworks is not the default schema and does not have db_owner rights.
2011-06-13 09:01:48,205 [SIBJMSRAThreadPool : 6] ERROR com.lombardisoftware.server.ejb.tracking.RepresentationManagerServicesBean -
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ CREATE TABLE teamworks.TG_SLATHRESHOLDTRAVERSALS (
Complete the following steps to correct the schema name:
- Shutdown the application (Teamworks, WebSphere Lombardi Edition, or IBM Business Process Manager)
- Log in to the database with a high level user, system administrator or equivalent
- Back up the database in the event you need to roll it back.
- Check to see if the database user has db_owner rights and that the default schema is the same as the user. By default, when creating new users in Microsoft SQL, the user's default schema is dbo. Make changes as necessary.
- Drop all Service Integration Bus (SIB) tables in the database. New SIB tables with the proper schema name are created when the servers start. There is no harm in dropping SIB tables on the process database. There is no in-flight instance or task data that is stored in SIB records.
- Run the SQL scripts to change tables, stored procedures, and views.
- Refresh the view database to confirm that all schema names are changed.
- Restart servers and confirm that the log files do not have any errors.
References:
Change Stored Procedure
Change Table Owner
Change Views
------------------------------
/*
This script changes the schema owner for stored procedures.
*/
DECLARE
@OldOwner sysname,
@NewOwner sysname
SET @OldOwner = 'dbo'
SET @NewOwner = 'lswbpm'
DECLARE CURS CURSOR FOR
SELECT
name
FROM
sysobjects
WHERE
type = 'p'
AND
uid = (SELECT uid FROM sysusers WHERE name = @OldOwner)
AND
NOT name LIKE 'dt%' FOR READ ONLY
DECLARE @ProcName sysname
OPEN CURS
FETCH CURS INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @@VERSION >= 'Microsoft SQL Server 2005'
BEGIN
EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
END
ELSE
EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')
FETCH CURS INTO @ProcName
END
CLOSE CURS
DEALLOCATE CURS
------------------------------
/*
This script changes the schema owner for all tables in a database.
*/
use bpmProcessDB /*which database to perfrom operation on.*/
DECLARE tabcurs CURSOR
FOR
SELECT 'dbo.' + [name] /*dbo here is the OLD schema name*/
FROM sysobjects
WHERE xtype = 'u'
OPEN tabcurs
DECLARE @tname NVARCHAR(517)
FETCH NEXT FROM tabcurs INTO @tname
WHILE @@fetch_status = 0
BEGIN
EXEC sp_changeobjectowner @tname, 'lswbpm' /*lswbpm here is the NEW schema and owner*/
FETCH NEXT FROM tabcurs INTO @tname
END
CLOSE tabcurs
DEALLOCATE tabcurs
------------------------------
/*
Example for single changes, in VIEWS, performance DB. These are the default views for the perfomance server. If other views exist from tracking tables, those will also need to be changed.
usage
EXEC sp_changeobjectowner 'oldschema.VIEW_NAME', 'newSchema'
*/
/*default ones for bpmPeformanceDB*/
EXEC sp_changeobjectowner 'dbo.PROCESSFLOWS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.SLASTATUS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.SLATHRESHOLDTRAVERSALS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.SNAPSHOTS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TASKS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TIMINGINTERVALS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TIMINGINTERVALVALUE', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKEDFIELDS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKEDFIELDUSE', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKINGGROUPS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKINGPOINTS', 'lswbpm'
EXEC sp_changeobjectowner 'dbo.TRACKINGPOINTVALUE', 'lswbpm'
