add users in a transaction SQL Server 2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maddog - 29 Mar 2007 16:08 GMT

I need to be able to add users to roles in serveral databases and if one of
the inserts fails I need to be able to roll back the whole transaction. I am
getting the following error:

The procedure 'sp_grantlogin' cannot be executed within a transaction.
The procedure 'sp_defaultdb' cannot be executed within a transaction.
The procedure 'sp_grantdbaccess' cannot be executed within a transaction.
The procedure 'sp_addrolemember' cannot be executed within a transaction.

Reply to this Message

Jens K. Suessmeyer - 31 Mar 2007 12:12 GMT

You will have to use your own logic to make it RollbackAble:

"sp_grantlogin cannot be executed within a user-defined transaction."
http://msdn2.microsoft.com/en-us/library/ms173449.aspx

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

>I need to be able to add users to roles in serveral databases and if one of
> the inserts fails I need to be able to roll back the whole transaction. I
[quoted text clipped - 5 lines]
> The procedure 'sp_grantdbaccess' cannot be executed within a transaction.
> The procedure 'sp_addrolemember' cannot be executed within a transaction.

Reply to this Message