No sp_password within a transaction
-
CREATE PROCEDURE ResetPassword (
@p_operid VARCHAR(8),
@p_newpassword VARCHAR(8),
@p_chgpwdnxtlogn VARCHAR(8) = 'Y',
@p_retcode INT OUT,
@p_errordesc VARCHAR(255) OUT,
@p_commit CHAR(1) = 'Y'
)
AS
BEGIN
/* UPDATE operator*/
/* Remove operator from hist table */
/* Assign new password to passhist */
/* Change user login */
IF @p_commit = 'Y' BEGIN
EXEC sp_password null, @p_newpassword, @p_operid
END
SELECT @p_retcode = 0,@p_errordesc = 'SUCCESSFUL'
END
GO
I have one sample SP like this.....
I am a vc developer calling this ResetPassword SP into my code....
Threre are some cases my sp_password may fail.....
so in those cases I have to rollback all my changes i have done previously...
that is update, delete operations etc...
In my code i can write Begin/commit/rollback transaction to rollback if the stored procedures fails and returns error code
but I know that i can't include sp_password within a transaction...so how can i rollback my operations that are executed.....
-
Hi,
You should need to use the SQL transaction in this SP and check either the sp_password generate the error and not if it generate then roll back the transaction otherwise commit the changes.
CREATE PROCEDURE ResetPassword (
@p_operid VARCHAR(8),
@p_newpassword VARCHAR(8),
@p_chgpwdnxtlogn VARCHAR(8) = 'Y',
@p_retcode INT OUT,
@p_errordesc VARCHAR(255) OUT,
@p_commit CHAR(1) = 'Y'
)
AS
BEGIN
DECLARE @Result INT
BEGIN TRANSACTION traPasswordReset
/* UPDATE operator*/
/* Remove operator from hist table */
/* Assign new password to passhist */
/* Change user login */
IF @p_commit = 'Y' BEGIN
EXEC @Result = sp_password null, @p_newpassword, @p_operid
END
IF @Result > 0
ROLLBACK TRANSACTION traPasswordReset
ELSE
ROLLBACK TRANSACTION traPasswordReset
SELECT @p_retcode = 0,@p_errordesc = 'SUCCESSFUL'
END
GO
I hope this will help to slove your problem
cheers
-
Thanks for your help ijaz.......
But It won't work, because we should not keep sp_password within a transaction like begin,commit,rollback.....it will fail
and it's giving error as.....The procedure sp_password cannot be executed within a transaction...that's why I am asking for any other alternative to rollback my operations(update,delete)
-
No body is there to help me further ?
-
Hi,
My only suggestion is for you to do what sp_password is doing. In other words, sp_password is just a stored procedure in the Master database that peforms tasks against various tables. Instead of relying on sp_password to perform those tasks, do them directly in your stored procedure.
If you run the following code in your Master database, sp_helptext sp_password, you will see the anatomy of the sp_password stored proc.
One table updated by this stored proc is the master.dbo.sysxlogins table. You could take the logic that the sp_password stored proc uses to the update that table and incorporate it directly into your stored proc. This will permit you to use a transaction so that if an error is encountered, then the entry into that table will be rolled back.
Alternatively, you could copy the text of the sp_password stored procedure and generate a similar stored procedure but comment out the following text:
-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sp_password')
return (1)
end
-
Thanks abdul,
Yes..It seems to solve my problem.
Actually i am not a db person, but I learned a lot from your mail.
I have one more doubt.
We are trying to lock and unlock the user logins to my application.
we have the users with Sqlserver authentication. Here we are using sp_grantlogin and sp_denylogin to lock and unlock the users. But it's failing because sp_grantlogin and sp_denylogin will only work for the windows authenticated users.
Is there any other alternative to perform lock/unlock on Sqlserver authenticated users.
-
"Thanks abdul,
Yes..It seems to solve my problem.
Actually i am not a db person, but I learned a lot from your mail"
Sometimes I am good for something.
Anyway, HTH."We are trying to lock and unlock the user logins to my application.
we have the users with Sqlserver authentication. Here we are using sp_grantlogin and sp_denylogin to lock and unlock the users. But it's failing because sp_grantlogin and sp_denylogin will only work for the windows authenticated users.
Is there any other alternative to perform lock/unlock on Sqlserver authenticated users."
In many of the projects that I've worked on, we design our own security tables in order to get around some of the MS restrictions.
However, have you tried sp_grantdbaccess and sp_revokedbaccess? This is the MS recommended way of granting and revoking access for SQL Server Login accounts.
Here's a link to the MSDN library and just click on the T-SQL link to view how MS uses those 2 stored procs.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4j77.asp
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply