Ensuring certain SPs get executed all together | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Ensuring certain SPs get executed all together

Just wondering how i can write a stored proc to ensure that certain SPs all get executed or the whole transaction rolls back. Problem is im using sp_grantlogin, sp_grantdbaccess, etc.. which cannot be defined in a user transaction. i.e.
begin tran
exec master.dbo.sp_grantlogin @pADGroup
select @error= @@error
if @error != 0
begin
raiserror(‘Add login failed’,16,1)
rollback tran
return
end WILL FAIL Any ideas? thx in advance..
The beauty of it is that if it fails, it fails. Huh? IOW you don’t have to rollback something that has failed.
quote:Originally posted by Adriaan The beauty of it is that if it fails, it fails. Huh? IOW you don’t have to rollback something that has failed.

no no, maybe i wasnt clear, i dont just have one of those blocks of code in my SP, i have many. For example, after the above one, id have another block of code that runs sp_grantdbaccess, then another block runnin sp_addrolemember.. i need all of them to run successfully and if one fails, they all rollback..
… then start nesting them with IF @@ERROR = 0 BEGIN … END statements, like so: exec master.dbo.sp_grantlogin @pADGroup
if @@ERROR = 0
begin
exec master.dbo.sp_grantdbaccess ……………..
if @@ERROR = 0
begin
exec master.dbo.sp_addrolemember ……………..
end
end

quote:Originally posted by Adriaan … then start nesting them with IF @@ERROR = 0 BEGIN … END statements, like so: exec master.dbo.sp_grantlogin @pADGroup
if @@ERROR = 0
begin
exec master.dbo.sp_grantdbaccess ……………..
if @@ERROR = 0
begin
exec master.dbo.sp_addrolemember ……………..
end
end

nope: Server: Msg 15002, Level 16, State 1, Procedure sp_grantlogin, Line 24
The procedure ‘sp_grantlogin’ cannot be executed within a transaction.

This is a workaround because you can’t have a transaction. Don’t include the transaction bits in your script, use the workaround instead.
… and BOL defines sp_grantlogin cannot be executed within a user-defined transaction. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
quote:Originally posted by Adriaan This is a workaround because you can’t have a transaction. Don’t include the transaction bits in your script, use the workaround instead.
… so if you need to rollback some step because one of later steps failed then you will need to code compensation/rollback logic yourself.
Let’s think for a moment about the circumstances under which any of the three steps would fail – provided that the login name, the database name and database role name are all valid … (1) sp_grantlogin
– fails if the login already exists (2) sp_grantdbaccess
– fails if the login already has access to the database, or if the login doesn’t exist (see 1) (3) sp_addrolemember
– fails if the login already belongs to the database role, or if the login doesn’t have access to the database (see 2), or if the login doesn’t exist (see 1) Again provided that the names (login, database, database role) are all correct, I don’t think you need to worry about any of these errors. Like mmarek suggested, if you have a series of logins that you want to add all-or-nothing, then you have to use some sort of cursor to be able to deny those logins, or at least revoke dbaccess.
]]>