How can we help you today? How can we help you today?

sp_revokedbaccess

In the SQL to remove User from a database the procedure sp_revokedbaccess is called after the transaction is commited(there is nothing to commit in the transaction) and after the comment 'The database update succeeded' has been printed.
/*
Script created by SQL Compare version 7.0.0 from Red Gate Software Ltd at ***.
Run this script on *** to make it the same as ***
Please back up your database before running this script
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
PRINT N'Dropping users'
GO
EXEC sp_revokedbaccess N'UserName'
GO
sangha
0

Comments

1 comment

Sort by
  • Andras B
    There are a few operations that must be called outside the transaction, like sp_addrolemember, most of the user opertaions on SQL Server 2000, fulltext catalog statements. These operations are not transactional, and cannot be run inside a transaction.

    Regards,
    Andras
    Andras B
    0

Add comment

Please sign in to leave a comment.