# stored procedure can select and update tables in other databases - minimal permissions granted

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
, 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
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
GO
GRANT SELECT ON dbo.xtblOrgUnt     TO [PartyOrderAlloc_HiddenUser]
GO
GRANT INSERT ON dbo.tblAudit       TO [PartyOrderAlloc_HiddenUser]
GO

USE [SAStockLevel]
GO
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;

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)

1) How could I have done this differently and more effectively?

This can be handled better by using what I call "code-based" security, instead of regular "Login/User-based" security. The idea is to essentially grant permissions to one or more modules (Stored Procedures, Triggers, most types of Functions, etc) instead of to Logins or Users. Then you grant EXECUTE permission to the module(s) to whatever User(s) and/or Role(s) are appropriate.

This is a much cleaner approach as it is far more granular/controllable and does not require IMPERSONATE permissions, EXECUTE AS, Cross-Database Ownership Chaining, or TRUSTWORTHY ON. Your existing Logins / Users can only do the very specific things that are coded into those modules, whereas granting them IMPERSONATE opens the door for that permission to be used outside of this intended purpose. And this is also more secure since using EXECUTE AS can also be used outside of this intended purpose, and will still be in effect if someone were to change the definition of the Stored Procedure. In contrast, when you sign a module, the signature is generated from the current definition of that module, including the WITH EXECUTE AS clause of the CREATE statement. Meaning, any change to either the module definition or the principal_id specified for WITH EXECUTE AS will invalidate the signature and the signature will thus be automatically dropped, resulting in the implied permissions no longer being granted.

You do this by creating either an Asymmetric Key or Certificate** , then create a Login and/or Users, depending on the need, based on that Key or Certificate, and then use that same Key or Certificate to sign the modules that should be able to do things that the users logging in shouldn't be able to do. Finally, you assign the desired permission(s) to the Login and/or User(s) as appropriate.

I have provided several examples and explanation across the following answers:

2) have you noticed that instead of writing the procedure with EXECUTE AS I had to add this line

This is because the EXECUTE AS clause of the CREATE PROCEDURE, CREATE FUNCTION, etc statements works on Users only, which are local to each DB. Logins are server-level. This will become irrelevant upon implementing what I am suggesting in #1.

3) MAYBE if both my login and user were domain accounts, would this make a difference?

That shouldn't make a difference. But again, this will become irrelevant upon implementing what I am suggesting in #1.

I don't want to add any database ownership chain, or trustworthy in order for this work, unless really necessary.

Neither of these are necessary when using signature-based / code-based security as suggested in #1.

** The main difference between Certificate (which contains a key) and Asymmetric Key is how easy is it to duplicate that key into other databases. If the need is very localized to a single database then an Asymmetric Key is fine. But if you need to manage this across multiple databases, or need a server-level permission, then it is usually easier to use a Certificate since they allow for backing up their key info into a file that can then be used to re-create that same Certificate in other databases, even on other instances. And starting in SQL Server 2012, you can extract the hex bytes that can then be used to recreate them. For Asymmetric Keys, the only way that I have found to create them with a consistent key across all databases is to embed that key info into an Assembly, then load that Assembly into a database, create the Asymmetric Key from the Assembly, and then if the Assembly is no longer needed, drop it. This is a natural approach when working with SQLCLR since you already have an Assembly and it should be signed (with a password -- .pfx file instead .snk). But when not working with SQLCLR, then just go with a Certificate.