I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum.
This was necessary because an specific application Stock Allocation would connect to the databases and only run this stored procedure.
The way I go this done is:
1) create a login that will be used to run the stored procedure in different databases
USE [master]
GO
CREATE LOGIN [PartyOrderAlloc_HiddenLogin] WITH PASSWORD=N'R4dh3R4dh3'
, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
2) Grant impersonate on the above login to the group of users that will run that procedure
GRANT IMPERSONATE ON LOGIN::[PartyOrderAlloc_HiddenLogin] TO [mycompany\it testing team]
GO
3) create the user based on the login above (1) and grant all the permissions required
USE [SAOrder]
GO
CREATE USER [PartyOrderAlloc_HiddenUser] FOR LOGIN [PartyOrderAlloc_HiddenLogin]
GO
--The SELECT permission was denied on the object 'tblSAOrderItemStatus', database 'SAOrder', schema 'dbo'.
GRANT SELECT ON dbo.tblSAOrderItemStatus TO [PartyOrderAlloc_HiddenUser]
GO
-- The UPDATE permission was denied on the object 'tblSAOrderItemStatus', database 'SAOrder', schema 'dbo'.
GRANT UPDATE ON dbo.tblSAOrderItemStatus TO [PartyOrderAlloc_HiddenUser]
GO
USE [SAStockData]
GO
CREATE USER [PartyOrderAlloc_HiddenUser] FOR LOGIN [PartyOrderAlloc_HiddenLogin]
GO
GRANT SELECT ON dbo.xtblOrgUnt TO [PartyOrderAlloc_HiddenUser]
GO
GRANT INSERT ON dbo.tblAudit TO [PartyOrderAlloc_HiddenUser]
GO
USE [SAStockLevel]
GO
CREATE USER [PartyOrderAlloc_HiddenUser] FOR LOGIN [PartyOrderAlloc_HiddenLogin]
GO
GRANT SELECT ON dbo.tblOrgGrpStockLevel TO [PartyOrderAlloc_HiddenUser]
GO
GRANT UPDATE ON dbo.tblOrgGrpStockLevel TO [PartyOrderAlloc_HiddenUser]
GO
GRANT SELECT ON dbo.tblOrgUntStockLevel TO [PartyOrderAlloc_HiddenUser]
GO
GRANT UPDATE ON dbo.tblOrgUntStockLevel TO [PartyOrderAlloc_HiddenUser]
GO
GRANT SELECT ON dbo.ztblAllocationBlockItems TO [PartyOrderAlloc_HiddenUser]
GO
4) Create the stored procedure on database [SAOrder]
use SAOrder
go
alter PROCEDURE spPartyOrderAllocation_AllocateItems(@strBxOrderNo varchar(20))
--with EXECUTE AS 'PartyOrderAlloc_HiddenUser'
AS
BEGIN
-- 18-Feb-2016 BAC Created spPartyOrderAllocation_AllocateItems
--
SET NOCOUNT ON;
EXECUTE AS LOGIN='PartyOrderAlloc_HiddenLogin'
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#YTAB')) DROP TABLE #YTAB
Declare @sintOrderSeqNo smallint
Declare @strItemNo varchar(20)
Declare @tintOrgGrpId tinyint
Declare @tintOrgUntId tinyint
--Select oders items that are on allocation hold and have stock in the W/H
Select OIS.strBxOrderNo, OIS.sintOrderSeqNo, OIS.strItemNo, SL.tintOrgGrpId, Null tintOrgUntId_Target
Into #YTAB
From SAOrder.dbo.tblSAOrderItemStatus OIS
Join SAStockLevel.dbo.tblOrgGrpStockLevel SL on SL.bigOrgGrpStkLvlId = OIS.bigOrgGrpStkLvlId
Join SAStockLevel.dbo.ztblAllocationBlockItems ABI on ABI.strItemNo = OIS.strItemno and ABI.tintOrgGrpId = SL.tintOrgGrpId
Where @strBxOrderNo = OIS.strBxOrderNo
And IsNull(OIS.sintAllocToOrgUntId,0) = 0
And OIS.sintSAItemStatus = 1
And SL.tintStockTypeId = 1
If @@ROWCOUNT = 0
Begin
Select '1'
Return
End
Declare cur_ytbIED_SAOrderAllocationReplenishment cursor forward_only for
Select
strBxOrderNo
, sintOrderSeqNo
, strItemNo
, tintOrgGrpId
From
#YTAB
Begin Transaction
Begin Try
Open cur_ytbIED_SAOrderAllocationReplenishment
Fetch Next From cur_ytbIED_SAOrderAllocationReplenishment
Into
@strBxOrderNo
, @sintOrderSeqNo
, @strItemNo
, @tintOrgGrpId
While @@FETCH_STATUS = 0
Begin
Begin
Select Top 1
@tintOrgUntId = tintOrgUntId
From
SAStockLevel.dbo.tblOrgUntStockLevel OUSL
Where
strItemNo = @strItemNo
And
tintOrgUntId In (Select tintOrgUntId from SAStockData.dbo.xtblOrgUnt Where tintOrgGrpId = @tintOrgGrpId and tintOrgUntId<>1)
And
lngTotal > (Case When lngAllocated > 0 Then lngAllocated Else 0 End)
Order By
lngTotal - (Case When lngAllocated > 0 Then lngAllocated Else 0 End) Desc
IF @@ROWCOUNT = 0
Begin
Delete #YTAB
Where strBxOrderNo = @strBxOrderNo and sintOrderSeqNo = @sintOrderSeqNo
End
Else
Begin
-- Reserve stock in the W/H by updating allocated stock on w/h row
print 'tintOrgUntId = ' + convert(varchar(3),@tintOrgUntId)
Update SAStockLevel.dbo.tblOrgUntStockLevel
Set lngAllocated = lngAllocated + 1
Where strItemNo = @strItemNo
And tintOrgUntId = @tintOrgUntId
-- Update y-table if w/h allocated to
Update #YTAB
Set tintOrgUntId_Target = @tintOrgUntId
Where strBxOrderNo = @strBxOrderNo
AND sintOrderSeqNo = @sintOrderSeqNo
-- Allocated to W/H so now insert to log indicating this has happened, In case we require to roll it back later
INSERT INTO tblSAOrderAllocationItemAllocateLog
([strItemNo]
,[tintOrgUntId]
,[sintQuantity])
VALUES (@strItemNo
,@tintOrgUntId
,1)
End
End
Fetch Next from cur_ytbIED_SAOrderAllocationReplenishment
Into
@strBxOrderNo
, @sintOrderSeqNo
, @strItemNo
, @tintOrgGrpId
End
Close cur_ytbIED_SAOrderAllocationReplenishment
Deallocate cur_ytbIED_SAOrderAllocationReplenishment
--Move the stock allocated from reserved to allocated
UPDATE SAStockLevel.dbo.tblOrgGrpStockLevel
SET lngReserved = lngReserved - sintQuantity
,lngAllocated = lngAllocated + sintQuantity
FROM SAStockLevel.dbo.tblOrgGrpStockLevel GSL
JOIN
(
SELECT
yOAR.strItemNo
, OU.tintOrgGrpId
, COUNT (*) AS sintQuantity
FROM #YTAB yOAR
JOIN SAStockData.dbo.xtblOrgUnt OU on OU.tintOrgUntId = yOAR.tintOrgUntId_Target
GROUP BY yOAR.strItemNo,OU.tintOrgGrpId
) ALog
ON ALog.strItemNo = GSL.strItemNo
AND ALog.tintOrgGrpId = GSL.tintOrgGrpId
AND GSL.tintStockTypeId = 1
UPDATE tblSAOrderItemStatus
SET sintAllocToOrgUntId = tintOrgUntId_Target
FROM tblSAOrderItemStatus OIS
JOIN #YTAB OA
ON OA.strBxOrderNo = OIS.strBxOrderNo
AND OA.sintOrderSeqNo = OIS.sintOrderSeqNo
INSERT INTO SAStockData.dbo.tblAudit(dtmDateTime,strMessage)
VALUES (Getdate(),'Manual Allocation Completed Successful')
End Try
Begin Catch
Select Error_Message()
IF @@TRANCOUNT > 0 Rollback
Return
End Catch
commit
Select '0'
END
GO
this has been working very well as it is. the results and behaviour is as expected.
question:
1) How could I have done this differently and more effectively?
2) have you noticed that instead of writing the procedure with EXECUTE AS I had to add this line
EXECUTE AS LOGIN='PartyOrderAlloc_HiddenLogin'
I know... I did not like it either, but it would not work with me using EXECUTE AS.
3) MAYBE if both my login and user were domain accounts, would this make a difference?
I don't want to add any database ownership chain, or trustworthy in order for this work, unless really necessary.
also:
we are still on sql-2005 on these test machines:
Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)
