Access 2007: Digital signatures are lost in ADE files

Some weeks ago I posted the following message in microsoft.public.access.security. Here I will reproduce and extend it, since I have not received any response there, to spread the words and reach a higher audicence. Maybe someone would give me a hint some day...

I also made a translation of this article in spanish called Access 2007: La firma digital se pierde cuando se compacta un .ADP o se crea un fichero .ADE

We have been using digital signatures for years (Office 2003) to deploy MS Access complied project files (.ADE) to avoid users to accept security warnings every time they opened the application.

Now we are using Office 2007 and whenever we create ADE files (with signed VB code), we receive (in Access status bar, on the bottom) the following warning (maybe the warning is not exactly as this, since it is a self-made translation from Spanish):

"The changes made to the databes or project had invalidated the linked digital signature"

"Las modificaciones realizadas en la base de datos o proyecto han invalidado la firma digital asociada." (this is the original warning).

After that, when any user tries to open the created ADE file, they receive the usual warning as if the file had no digital signature at all:

Microsoft Office Access Security Notice A potential security concern has been identified. Warning: it is not possible to determine that this content came from a trustworthy source. You should leave this content disabled unless the content provides critical functionality and you trust its source.

That dialog confirms that the original warning shown in the status bar was right. The signature is lost when you compile the ADP into an ADE file.

I have checked that this happens when creating a brand new database project:

1. Create a new database project (ADP) and connect it to any SQL Server / Northwind you have at hand.
2. Create a form with a single button in it and use the following code:

   Option Compare Database
   Option Explicit
   Private Sub Command0_Click()
   MsgBox ("this is a test")
   End Sub

3. Sign the code: Tools -> Digital signatures...
4. Create ADE
5. Try to open the ADE you created before

Can this be classified as a bug or a feature? Is there any place to submit feedbacks for Access 2007?

Thanks in advance.

To this post, and after having done more tests, I would also say that a similar behaviour is shown whenever you compact and repair an ADP file the digital signature is lost too.

It does not matter that there are alternative ways to prevent the security warnings from appearing (i.e. Trust Center). I think this is a real bug that prevents anyone that owns a Digital Certificate from signing and distributing code (without distributing the code, i.e. ADE files) the way we were used to do with Access 2003.

I hope staff at MS solve it better sooner than later. I'm also willing to hear from your experiences on this field.

Links

Access 2007: Digital signature is lost when you create ADE files in Access Security

Access 2007: La firma digital se pierde cuando se compacta un .ADP o se crea un fichero .ADE

Access 2007: Digital signatures are lost in ADE files in Access Database General Questions

Access 2007: Digital signatures are lost in ADE filesin Access ADP SQL

Identity columns in a view treated as an ordinary int column

I was experiencing the behavior shown in KB920974: FIX: SQL Server 2005 treats an identity column in a view as an ordinary int column when the compatibility level of the database is set to 80 and asked Microsoft support for the hotfix opening a new support incident. After some hours, technical staff sent me the links for the files and installed them on a virtualised environment (testing is always a good thing).

After having checked that the problem was solved if the database compatibility level was 80, I changed it back to 90 and the problem appeared again. I did not accept the ms incident as being resolved and still complained about it. They told me that the shown behaviour was BY DESIGN. In other words, if you are using a database with compatibility level 80, your identity fields will still behave as identity when viewed through a view (check the following example in how to reproduce the problem), but if you want to use compatibility level 90, the identity behavior is lost by design if the table is viewed through a view. I could not believe it! Why did they do that?

As stated in SQL Server 2005 BOL regarding Modifying Data Through a View:

You can modify the data of an underlying base table through a view, in the same manner as you modify data in a table by using UPDATE, INSERT and DELETE statements or by using the bcp utility and BULK INSERT statement. However, the following restrictions apply to updating views, but do not apply to tables:

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
• The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:
  • An aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP).
  • A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) amount to a computation and are also not updatable.
• The columns that are being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.
• TOP cannot be used anywhere in the select_statement of the view when WITH CHECK OPTION is also specified.

[...] The following additional guidelines apply:[...]

• INSERT statements must specify values for any columns in the underlying table that do not allow null values and have no DEFAULT definitions.

Steps to reproduce the problem: Create a new [test] database in compatibility mode 90 (2005). Define the following two tables: TMaterials and TMeasureUnits with the script:

CREATE TABLE [dbo].[TMeasureUnits](
 [MeasureUnitId] [int] NOT NULL,
 [Description] [varchar](50) COLLATE Modern_Spanish_CI_AS NOT NULL,
 CONSTRAINT [PK_TMeasureUnits] PRIMARY KEY CLUSTERED 
(
 [MeasureUnitId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TMaterials](
 [MaterialId] [int] IDENTITY(1,1) NOT NULL,
 [Description] [varchar](50) COLLATE Modern_Spanish_CI_AS NULL,
 [MeasureUnitId] [int] NOT NULL,
 CONSTRAINT [PK_TMaterials] PRIMARY KEY CLUSTERED 
(
 [MaterialId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TMaterials] WITH CHECK
  ADD CONSTRAINT [FK_TMaterials_TMeasureUnits] FOREIGN KEY([MeasureUnitId])
  REFERENCES [dbo].[TMeasureUnits] ([MeasureUnitId])
GO
ALTER TABLE [dbo].[TMaterials] CHECK CONSTRAINT [FK_TMaterials_TMeasureUnits]
GO
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (1, 'Units')
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (2, 'Boxes')
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (3, 'm.')
INSERT INTO [dbo].[TMeasureUnits] (MeasureUnitId, Description) VALUES (4, 'Kg.')

Now we will create the offending view:

CREATE VIEW [dbo].[VMaterials]
AS
SELECT dbo.TMaterials.MaterialId,
       dbo.TMaterials.Description,
       dbo.TMaterials.MeasureUnitId,
       dbo.TMeasureUnits.Description AS MeasureUnit
FROM dbo.TMaterials INNER JOIN
     dbo.TMeasureUnits ON dbo.TMaterials.MeasureUnitId = dbo.TMeasureUnits.MeasureUnitId
GO

Now execute:

SELECT name, is_identity
FROM sys.columns
WHERE object_id = object_id('VMaterials')

And you will see that NONE of the fields have is_identity = 1. This behavior is told to be BY DESIGN if database compatibility level 90. The fix is only intended to restore original behavior when database is in level 80 (2000).

The real problem: Despite this different behavior, the view is still insertable using T-SQL. Our real problem then, is due to MS Access.

We are using MS Access 2003 as front-end for our SQL Server 2005 corporate database. With such a new behavior, MS Access 2003 is not able to do INSERTs in such views. When MS Access asks SQL Server for view's metadata (to know field types, lengths, primary keys, nullable fields, and identities), SQL incorrectly returns that the identity field is not an identity field anymore:

1. Create a new MS Access 2003 database project and connect to your server and database.
2. Change to see Queries objects and open VMaterials (note that the expected first field should be marked as (Autonumeric) and it is not).
3. Try to insert a new record:
   • If you try to leave MaterialId blank, and enter Description='Sand', MeasureId=2, you will get the error: Invalid input parameter values. Check the status values for details.
   • If you try to give any value to MaterialId and Description='Sand', MeasureId=2 as before, you get the following error message: Cannot insert explicit value for identity column in table 'TMaterials' when IDENTITY_INSERT is set to OFF

Up to now, our incident is still opened and no solution nor workaround has been given yet (apart from the obvious fall back to compatibility level 80).

Grant VIEW DEFINITION permission to tables and/or views

SQL Server 2005 still supports all of the fixed server and database roles introduced in SQL Server 2000, along with their default permissions. However, custom database roles, application roles, as well as server logins and database users can be granted (or denied) more granular permissions on a much wider range of objects. Some of the most commonly used permission types present in earlier version of SQL Server, such as EXECUTE, SELECT, or TAKE OWNERSHIP offer new possibilities in their new implementation (since they can be applied on different levels and offer inheritance capabilities). There are also others, newly introduced ones, including, for example:

• CONTROL - functionally equivalent to all permissions granted to the object's owner and inherited by all subentities within its scope,
• ALTER - provides the ability to alter properties of an object. Depending on the scope, you can limit its inheritance to objects of a specific type (for example, its variation in the form ALTER ANY 'object_type' grants permissions to modify every instance of 'object_type' within server or database scope). ALTER TRACE allows running Profiler without membership in SysAdmin fixed server role.
• IMPERSONATE - as mentioned earlier, permits impersonating another user (without requiring SysAdmin or dbo privileges, as was the case in SQL Server 2000),
• and VIEW DEFINITION

According to BOL about VIEW DEFINITION Permission:

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

And now the reasons for this blog entry: If you are using MS Access as your front end for SQL Server 2005, and you have tables with not-nullable columns with default values, as for instance:

CREATE TABLE [dbo].[MyTable] (
  [Id] [int] NOT NULL,
  ... more columns...
  [ColumnName] [int] NOT NULL CONSTRAINT [DF_TMyTable_ColumnName] DEFAULT ((0))
  CONSTRAINT [PK_TMyTable] PRIMARY KEY NONCLUSTERED
  ( [Id] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

your MS Access application will not be aware of the default values for columns in the table and your insertions will fail unless the client manually sets a value for those default values. According, to BOL on Viewing Object Definitions, this is by design:

If the user is not the table owner of myTable and has been granted only SELECT permission on the table, when the user accesses the following catalog views, the definition columns are NULL.

Catalog view name	Transact-SQL definitions for
sys.sql_modules	All triggers in myTable
sys.computed_columns	All computed column in myTable
sys.check_constraints	All CHECK constraints in myTable
sys.default_constraints	All DEFAULT constraints in myTable

If you, your application or your users were used to work with SQL Server 2000 this will appear as a new behaviour, since granting SELECT access to a table in SQL Server 2000 also granted to read the structure of the table itself. Now, in SQL Server 2005, this is a different right and has to be granted explicitly. In fact it is the least privilege: you can only read the structure of the object (securable) but nothing else, you cannot even read data (SELECT). To solve the problem with MS Access not inserting the default values for tables and views, you simply have to grant users the right to VIEW DEFINITION of the tables/views. Keywords: SQL Server 2005, identity, not null, not nullable, default, default value, constraint, view definition, ms access, problem, issue, insert, select, permission.

Problems with identity fields in MS Access when using SQL Server's transactional replication with queued updates

Scenario: Transactional replication among SQL Server 2005 publisher and 2000 or 2005 subscribers with queued updates. Articles/Tables with a primary key defined by an identity field throw errors in MS Access when inserted at the subscriber (SQL Server 2005 Standard SP1 & MS Access 2003 SP2).

Problem description: @@identity is changed by replication triggers deployed to subscribers.

Side effects: Since MS Access 2003 uses @@identity (see BOL for more information) to retrieve the last inserted record for tables indexed by identity fields, every time you try to insert a new record on a subscriber of transactional replication with queued updates on a table with an identity primary key, the following error message appears: "The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source."

Good solution (but out of our hands): MS Access 2003 should use SCOPE_IDENTITY() (see BOL) instead of @@identity to retrieve the record that it has just inserted. Since @@identity is not scope-aware, if there are triggers in the offending table that do other inserts on other tables also managed with identities, @@identity would return the wrong value to client application (MS Access). SCOPE_IDENTITY() solves this problem by returning the last inserted identity within the same scope. Since this behavior is hard-coded inside MS Access there is very little we can do with user defined code (VB) to change this. Until a MS Office Service Pack solves this issue, the only things that programmers can do are workarounds.

Workarounds:

a. Do not use identity as PK on tables. Sometimes identities are avoidable, some other times not. If you have read up to this point, you probably need them and not using them is not an option for you.

b. Create triggers on tables experiencing this problem to discard inserts done by MS Access and do the real inserts through VB code inside a form. First of all, you need a way for the trigger to know when the insertion is done by MS Access directly and when the insertion comes through your user defined code. For this purpose you can use SET CONTEXT_INFO as in the following VB code (self documented):

Private ResyncCommandORI As String
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strSQL As String
    Dim rst As ADODB.Recordset
    Dim NewIdentity As Long

    If (Me.NewRecord) Then ' We are inserting
        ' Set this context variable so that the trigger knows the insertion
        ' is done via VB. If this context variable is not set, the trigger
        ' will delete the inserted record transparently.
        CurrentProject.Connection.Execute "SET CONTEXT_INFO 0x1"

        strSQL = "INSERT INTO TestTable (TestField) " & _
                 "VALUES ('" & TestField.Value & "'); SELECT SCOPE_IDENTITY() AS Id"
        Set rst = CurrentProject.Connection.Execute(strSQL)

        ' Once the insertion is done, we delete the context info
        CurrentProject.Connection.Execute "SET CONTEXT_INFO 0x0"

        ' And now we retrieve the fresh new identity
        ' Skip first recordset, closed and without data (insert statement)
        Set rst = rst.NextRecordset
        If (rst.State <> adStateClosed) Then
            If Not rst.EOF Then
                NewIdentity = rst.Fields(0)
            End If
        End If

        ' In order to MS Access copes with the insertion that he thinks
        ' he will be able to do without problems, we need to change
        ' ResyncCommand so that it does not use ? (@@identity) and
        ' use the NewIdentity we have just read.
        ResyncCommandORI = Me.ResyncCommand
        Me.ResyncCommand = Replace(Me.ResyncCommand, "?", Trim(Str(NewIdentity)))
        ' ResyncCommand is ready to be used by MS Access so that it
        ' can read the new record.
    End If
End Sub
Private Sub Form_AfterInsert()
    ' We just leave ResyncCommand as it was originally (with the ?)
    ' so that updates will work also.
    Me.ResyncCommand = ResyncCommandORI
End Sub

Besides this VB user code in every form dealing with tables with identities, we need to create the following trigger on those tables:

CREATE TRIGGER [dbo].[insert_TestTable_trigger] on [dbo].[TestTable]
   AFTER INSERT
   NOT FOR REPLICATION
AS
BEGIN
    DECLARE @v varbinary(1024) -- 128bytes

    -- added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON
    SELECT @v = context_info
      FROM master.dbo.sysprocesses
      WHERE spid = @@SPID;

    IF @v <> 0x1 BEGIN
      -- Do not complete the insertion. If we do a ROLLBACK MS Access
      -- will know and will raise an error. We just do a DELETE of the
      -- record just inserted and nothing wrong happens.
      DELETE dbo.TableTest
        FROM dbo.TableTest
        INNER JOIN inserted ON dbo.TableTest.Id = inserted.Id
    END
END

Using this code in every form and in every table that uses identities is a hard work and a nasty workaround. You will agree that this is not an elegant nor scalable solution and it is not a good idea to mix this complex logic between client (MS Access) and server (SQL Server). If you are not satisfied with this workaround, please keep on reading...

c. Modify SQL Server 2005 core system stored procedures to leave @@identity unchanged on sync triggers deployed to subscribers. SQL Server has two kind of transactional replication modes: immediate and queued. When in immediate mode, every subscriber connects to publisher in every insert/update/delete using a two-phase protocol (2PC) so that the statement is only 'approved' for the subscriber when it has been committed previously on the publisher. When transactional replication drops into queued mode (because of a network connection loss or intentionally because you want it that way), the changes are queued and they are tried to be applied on a scheduled basis, hence a little (but selectable) latency occurs. No matter which mode you use, the identities are generated (by default) at the side doing the insertion and that is the reason for them to need a reserved space for assigned identities (identity ranges) so that there are no collisions (conflicts). This is another changed behavior since SQL Server 2000. When using SQL Server 2000 and transactional replication with immediate updates, identities were generated always at the publisher even though the insertions were done on a subscriber. In 2005, identity ranges are used always (by default), even when using immediate updates. In order to implement replication SQL Server deploys a series of stored procedures and triggers to subscribers. The names for those triggers and stored procedures are like:

trg_MSsync_del_TestTable
trg_MSsync_ins_TestTable
trg_MSsync_upd_TestTable
sp_MSdel_dboTestTable
sp_MSins_dboTestTable
sp_MSupd_dboTestTable

It seems that, internally, the *ins* trigger or stored procedure involved during an insertion do another insert into a table with identities, thus, changing the value of @@identity and making it unusable for MS Access afterwards. As I said before, it's MS Access fault but, as long as we cannot change MS Access behavior, we will modify SQL Server core system stored procedures that create the scripts that create the former triggers so that @@identity is preserved when they are executed. Our work is based on a trick widely used by programmers to circumvent MS Access bad behavior (thanks Sylvain Lafontaine for your information on my post at news microsoft.public.access.adp.sqlserver). This trick is commented in this post and basically the idea is saving @@identity on a local variable right at the beginning of the trigger and then restore it before leaving out such as:

CREATE TRIGGER [dbo].[trg_MSsync_ins_TestTable] on [dbo].[TestTable]
AFTER INSERT
NOT FOR REPLICATION
AS BEGIN
 DECLARE @identity int, @strsql varchar(128)
 SET @identity=@@identity
   -- Main block of the trigger here
 SET @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ', 1) as id into #tmp'
 EXECUTE(@strsql)
END

This is the trick that people have been using on their own triggers. Making this goes against the definition of @@identity, that must always return the last inserted identity in any table for the current connection, but as I said, this is a trick and it does the job (MS Access does the inserts without any error despite its own bug). The main challenge for us now is that the offending triggers are not user-generated, but system-generated. Where do I need to 'touch' to modify SQL Server's behavior and include those 4 lines of code into their own generated triggers? That's the question. And here is the answer: The Resource Database (or RDB). I first read about it here and here are some official words from Microsoft:

[...]The Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.[...]

First of all, I must say that the following procedure is completely unsupported by Microsoft. As stated in the former URL:

[...]The Resource database should only be modified by or at the direction of a Microsoft Customer Support Services (CSS) specialist. The only supported user action is to move the Resource database to the same location as the master database. For more information, see Moving System Databases.[...]

The (unsupported) steps to make things work:

1. Do a backup. As you have probably guessed, the first thing you should do before making any unsupported change is a backup. Do a backup of your databases and (more important) do a backup of your original files mssqlsystemresource.mdf and mssqlsystemresource.ldf. They must be in the same directory as your master database files, usually %PROGRAMFILES%\Microsoft SQL Server\MSSQL.1\MSSQL\Data. Since that database is read only, there should be no problem if you try to do the backup with mssqlserver service running, but just for security, stop the service and do the backup afterwards (from a cmd line):

net stop mssqlserver
copy mssqlsystemresource.mdf mssqlsystemresource.mdf.original
copy mssqlsystemresource.ldf mssqlsystemresource.ldf.original

The backup could be as simple as that. You can try this whole procedure on a virtualised / testing environment for improved security.

2. Start SQL Server in single-user mode. In order to modify the resource database, we need to start SQL Server in single-user mode (/m parameter). We need to stop SQL Server Agent and any other service that is SQL Server related (i.e. reporting services, analysis services, IIS if apply) so that the agent (or other) could use the unique allowed connection.

net stop sqlserveragent
net stop (any other service related)
net start mssqlserver /m

3. Use sqlcmd to make the changes. This is the core of all this article. You need now to connect to the server and execute the ALTER PROCEDURE to modify/patch the system stored procedures sys.sp_MSscript_sync_del_trig, sys.sp_MSscript_sync_ins_trig and sys.sp_MSscript_sync_upd_trig. You can download the full source code for patching SQL Server 2005 SP1 here: sp_MSscript_sync_xxx_trig_for_SQL_Server_2005_SP1_9.00.2047.00.zip. Unzip the files and run the following commands:

sqlcmd -U sa -P yoursapass -A -d master -i sp_MSscript_sync_del_trig.sql
sqlcmd -U sa -P yoursapass -A -d master -i sp_MSscript_sync_ins_trig.sql
sqlcmd -U sa -P yoursapass -A -d master -i sp_MSscript_sync_upd_trig.sql

If everything has gone nicely, you should be again on the command prompt. Now you just need to stop the service and start it again as usual (not in single-user mode).

net stop mssqlserver
net start mssqlserver
net start (anything previously stopped)

Congratulations! Now the system stored procedures sys.sp_MSscript_sync_xxx_trig contain our custom pieces of code. You won't need to follow this procedure for every server involved in replication as long as they are all SQL Server 2005 with the same service pack applied. You just need to copy the new (updated) mssqlsystemresource.mdf and mssqlsystemresource.ldf files to every server/instance (stop service first). Furthermore, the files are the same for 32bit versions and x64 versions of SQL Server 2005 (you can check it using any crc32, md5 file checker as I did). It is important that every subscriber have this patched version of resource database files since the patched stored procedure is called at the subscribers. When a publication is marked for reinitailization at the publisher and the initial snapshot is run, the scripts sent to the subscribers do not include the offending triggers, they are generated and applied directly at the subscribers. The initial snapshot publication files include:

• bulk copy files with the contents of the tables (.bcp)
• DDL commands to recreate the tables, sp_MSxxx_ stored procedures and schema (.sch)
• Constraints dealing with identity ranges repl_identity_range_tran_xxx (.dri)
• Indexes and primary key constraints (.idx)
• Pre-script to drop foreign keys pointing to the table we are publishing and need to drop and recreate (.pre)
• User defined triggers (.trg)

As you can see, there is no trace about the triggers that appear finally on subscribers. That is because they are generated and applied locally on the subscribers and that is the reason because every subscriber must have the patched files.

4. The final step. Reinit every publication that you had already running so that the new patched triggers are generated and applied on every subscriber. After that, MS Access should work nicely without any problem dealing with identities.

Some final words (disclaimer): As I already stated, this patching procedure is completely unsupported by Microsoft. I shall not be liable for any damage (including, without limitation, damage for loss of business or loss of profits) arising from the use of, or inability to use, this document or any material contained in it, or from any action or decision taken as a result of using this document or any such material. I tested this procedure in my environment, solved the problem and worked as expected. I encourage you to do your own test in virtualised/testing environments first. Before applying any new SQL Server Service Pack that might arise in the future, I suggest to stop the server, restore the original files we copied at step 1 of the procedure, and then follow the instructions of the SP. After the SP is applied (also on subscribers), you can reinit the publications and see if the MS Access error appears again. If so, I suppose you could re-patch the new resource database that the SP might have set in place. In my scenario SQL Server 2005 was Spanish localized and a SELECT SERVERPROPERTY('ResourceVersion'), SERVERPROPERTY('ResourceLastUpdateDatetime') returned: Original RDB: 9.00.2047 2006-04-14 04:17:29.497 Patched RDB: 9.00.2047 2006-06-15 19:08:00.210

Note for SQL Server 2000 subscribers: Solving this issue for SQL Server 2000 subscribers is much more easier. We need to modify the same dbo.sp_MSscript_sync_xxx_trig system stored procedures, located at the master database. No need to deal with Resource Databases, needing to stop and start the server in single-user mode, etc... just a simple ALTER PROCEDURE and a reinitialization of the publications. Well, there are some additional steps to be taken in order to still have those stored procedures marked as system objects. If you just do an ALTER PROCEDURE, they will no longer be marked as system objects. The complete scripts for patching SQL Server 2000 SP4 are available here.

Links: You receive an error message when you add a record to a subform in an Access 2002 Project Moving System Databases (SQL Server 2005) http://msdn2.microsoft.com/en-us/library/ms187342.aspx BOL chapter about SCOPE_IDENTITY() function

My feedback to Microsoft on this issue: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125475

Keywords: Identity, @@IDENTITY, transactional replication with queued updates, SQL Server 2005, Microsoft, MS Access, SCOPE_IDENTITY(), Reserved Resource Database, sp_MSscript_sync_ins_trig, bug, error, workaround, resolution

NOTE: Just as sp_MSscript_sync_ins_trig purpose is scripting the trg_MSsync_ins_TableName trigger on subscribers, trg_MSsync_upd_TableName is scripted by sp_MSscript_sync_upd_trig, trg_MSsync_upd_TableName is created by sp_MSscript_sync_del_trig and the all three need to be patched the same way. All of them make inserts into MSreplication_queue table, which has an identity column called orderkey. You can read about MSreplication_queue table on SQL Server 2005 Books Online. Any command that should be delivered to publisher is inserted into this table, thus changing the value of @@identity variable. Now I have the three stored procedures (sp_MSscript_sync_ins_trig, sp_MSscript_sync_upd_trig and sp_MSscript_sync_del_trig) patched and everything is running smoothly.