October 18, 2011 at 7:23 am
I have some TSQL that uses TRY / CATCH.
Inside the TRY there are lots of security assignments. If one fails, I want to raise an error but I also want to carry on with the next assignment. Without the TRY / CATCH I can just use a GO statement to terminate the batch and carry on
Is there some equivalent with TRY / CATCH or do I just need to create separate TRY / CATCHES for each one?
Thanks
Steve
October 18, 2011 at 8:27 am
Please post your SQL query here.
October 18, 2011 at 8:32 am
Post code, but probably try..catch for each one because they're separate batches.
Alternative is do something like Powershell to make the changes instead with error handling in there.
October 18, 2011 at 8:33 am
Something like:
USE DBName
GO
BEGIN TRY
SET NOCOUNT ON
DECLARE @ErrorMessage NVARCHAR(300)
--XXXXXX---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'UserName')
DROP USER [UserName]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'UserName')
BEGIN
CREATE USER [UserName] FOR LOGIN [LoginName] WITH DEFAULT_SCHEMA = dbo
EXEC sp_addrolemember N'db_datareader', N'UserName'
EXEC sp_addrolemember N'db_denydatawriter', N'UserName'
END
-----------------------------------------------------------------------------------------
--XXXXXX---------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'UserName')
DROP USER [UserName]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'UserName')
BEGIN
CREATE USER [UserName] FOR LOGIN [LoginName] WITH DEFAULT_SCHEMA = dbo
EXEC sp_addrolemember N'db_datareader', N'UserName'
EXEC sp_addrolemember N'db_datawriter', N'UserName'
EXEC sp_addrolemember N'db_ddladmin', N'UserName'
END
-----------------------------------------------------------------------------------------
END TRY
BEGIN CATCH
SET @ErrorMessage = 'Error Line ' + CAST(ERROR_LINE() AS VARCHAR(5)) + ': ' + ERROR_MESSAGE()
RAISERROR (@ErrorMessage,
16, -- Severity.
1 -- State.
) WITH LOG
END CATCH
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply