April 1, 2010 at 11:51 am
Hi guys,
I have this looong SP that I need to run with elevated privileges but not from the context of the restricted user the application runs under. Basically I need to be able to run SET IDENTITY_INSERT tablename ON inside the stored procedure without granting sysadmin/control server privileges to my application user.
Can someone tell me how should I approach this?
Here's the code:
if exists (select * from sys.objects where object_id = object_id(N'[dbo].[sp_UpdateLegacyTargetId_test]') and type in (N'p', N'pc'))
drop procedure [dbo].sp_UpdateLegacyTargetId_test
go
set ansi_nulls on
go
set quoted_identifier on
go
create procedure sp_UpdateLegacyTargetId_test
(
@OldLegacyTargetId int,
@NewLegacyTargetId int
)
with execute as 'ChangeContextForUpdate'
as
begin
set nocount on;
set transaction isolation level serializable
set xact_abort on
declare @ReturnErrorMessage varchar(1000)
begin try
begin transaction
--creating the #TargetHolder table to hold the temporary data
if object_id('tempdb..#TargetHolder') is not NULL
drop table #TargetHolder
create table #TargetHolder
(
[TargetId] [bigint] NOT NULL,
[LegacyTargetId] [int] NOT NULL,
[Name] [varchar](255) NULL,
[TitanOffer] [bit] NULL,
[OfferId] [bigint] NULL,
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[CpanelOfferId] [int] NULL
)
--collecting the data to be updated.
insert into #TargetHolder
select [TargetId]
,[LegacyTargetId]
,[Name]
,[TitanOffer]
,[OfferId]
,[DateCreated]
,[DateModified]
,[CpanelOfferId]
from [Target]
where LegacyTargetId = @OldLegacyTargetId ;
--updating LegacyTargetId with the desired value
update #TargetHolder
set LegacyTargetId = @NewLegacyTargetId
--deleting the entire row from the Target table for the existing LegacyTargetId value
delete from Target
where LegacyTargetId = @OldLegacyTargetId;
--... and inserting whatever is stored on #TargetHolder temp table into Target table
set identity_insert Target on;
insert into Target
(
[TargetId]
,[LegacyTargetId]
,[Name]
,[TitanOffer]
,[OfferId]
,[DateCreated]
,[DateModified]
,[CpanelOfferId]
)
select[TargetId]
,[LegacyTargetId]
,[Name]
,[TitanOffer]
,[OfferId]
,[DateCreated]
,getdate()
,[CpanelOfferId]
from #TargetHolder
set identity_insert Target off
if object_id('tempdb..#TargetHolder') is not NULL
drop table #TargetHolder
commit transaction
end try
begin catch
if (xact_state()) = -1
begin
declare
@errornumberint,
@errorseverityint,
@errorstateint,
@errorprocedure nvarchar(max),
@errorlinenvarchar(100),
@errormessagenvarchar(max)
select
@errornumber=error_number(),
@errorseverity=error_severity(),
@errorstate =error_state(),
@errorprocedure=error_procedure(),
@errorline =error_line(),
@errormessage=error_message()
rollback transaction
raiserror (@errormessage,@errorseverity,@errorstate)
end
if (xact_state()) = 1
begin
commit transaction
end
end catch;
end
Thanks a bunch in advance,
Daniel
April 2, 2010 at 6:57 am
1. Change the EXECUTE AS clause
2. Create a certificate or asymmetric key, create a user from that, grant the required permissions to that user, then sign the procedure with the certificate or key using ADD SIGNATURE
On a separate subject...can I ask what the purpose of the procedure is?
It seems to be changing the value of an IDENTITY column in the scariest way possible...:Wow:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 2:35 pm
I still have my eyebrows up since I've learned what the heck is the dev doing... But so be it, it falls under special requests section not to be debated upon.
The dev crossed his heart that this special update won't be called more than once a week, therefore I consider the compromise acceptable.
On another note, I'm still fuming that in comparison with Oracle, in SQLServer is a real burden to accomplish this. The guy from M$ who designed this approach should be whacked on the spot mercilessly. Not to say that MySQL is better but at least, under special circumstances, an autoincrement value can be updated, no brainer style.
April 2, 2010 at 2:37 pm
Forgot to say thanks for the solution, I'm itching to see it implemented.
April 3, 2010 at 1:26 am
Daniel C (4/2/2010)
Forgot to say thanks for the solution, I'm itching to see it implemented.
No worries, though I think the implementation more complex than it needs to be:
Setup:
IF OBJECT_ID(N'tempdb..#Test', N'U')
IS NOT NULL
DROP TABLE #Test;
GO
-- Test table
CREATE TABLE #Test
(
target_id BIGINT NOT NULL
IDENTITY (100, 1)
PRIMARY KEY,
legacy_id INTEGER NOT NULL,
name VARCHAR(50) NULL,
titan_offer BIT NULL,
offer_id BIGINT NULL,
created_dt DATETIME NULL,
);
-- One test row target_id = 100
INSERT #Test
(legacy_id, name, titan_offer, offer_id, created_dt)
VALUES (1001, 'Some name', 0, 123456, CURRENT_TIMESTAMP);
-- Show the row
SELECT *
FROM #Test;
Identity-change implementation
SET XACT_ABORT ON;
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
SET IDENTITY_INSERT #Test ON;
-- Hold an update lock on the source row
-- for the length of the transaction
INSERT #Test
(target_id, legacy_id, name, titan_offer, offer_id, created_dt)
SELECT 10, T.legacy_id, T.name, T.titan_offer, T.offer_id, T.created_dt
FROM #Test T WITH (UPDLOCK)
WHERE T.target_id = 100;
-- UPDLOCK means this will succeed
DELETE #Test
WHERE target_id = 100;
SET IDENTITY_INSERT #Test OFF;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Error handling goes here
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH;
-- Show the result of the change
SELECT *
FROM #Test;
GO
-- Tidy up
DROP TABLE #Test;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply