I'm currently having issues relating to permissions when attempting to run a stored procedure that tries to select from a system table. Basically I'm trying to work around the deprecation of the sp_bindtoken system stored procedure in SQL Server 2012 by grabbing the data I need from the sys.dm_tran_current_transaction table directly.
One solution I've found is to:
This approach seems to work fine in SQL Server 2008 R2. However in SQL Server 2012, even though the script appears to run correctly, at runtime the stored procedure fails with a permissions error when attempting to select from the above system table.
USE OurDatabase
GO
CREATE CERTIFICATE OurDatabaseProcCert
FROM FILE = 'C:\Path\To\OurDatabaseProcCert.cer'
WITH PRIVATE KEY (
FILE = 'C:\Path\To\OurDatabaseProcCert.pvk',
ENCRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd',
DECRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd');
GO
USE master
GO
CREATE CERTIFICATE OurDatabaseProcCert
FROM FILE = 'C:\Path\To\OurDatabaseProcCert.cer'
WITH PRIVATE KEY (
FILE = 'C:\Path\To\OurDatabaseProcCert.pvk',
ENCRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd',
DECRYPTION BY PASSWORD = '$0m3$tr0ngp@$$w0rd');
GO
CREATE LOGIN OurDatabaseServerLogin
FROM CERTIFICATE OurDatabaseProcCert
GO
CREATE USER OurDatabaseServerLogin
REVOKE CONNECT SQL FROM OurDatabaseServerLogin
GO
GRANT AUTHENTICATE SERVER TO OurDatabaseServerLogin
GO
GRANT VIEW SERVER STATE TO OurDatabaseServerLogin
GO
USE OurDatabase
GO
ADD SIGNATURE TO dbo.bsp_getTransactionID BY CERTIFICATE OurDatabaseProcCert WITH PASSWORD = '$0m3$tr0ngp@$$w0rd'
CREATE Procedure bsp_getTransactionID
(
@TransactionID VARCHAR(255) OUTPUT
)
AS
BEGIN
IF @@TRANCOUNT > 0
BEGIN
SELECT SYSTEM_USER
SELECT @TransactionID = sys.dm_tran_current_transaction.transaction_id FROM sys.dm_tran_current_transaction
END
RETURN 0
END
GO
Has anyone run into this type of problem before?
To grant permissions on a stored procedureFrom Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role, select Search. In Select Users or Roles, select Object Types to add or clear the users and roles you want.
Right-click the certificate, select All Tasks > Manage Private Keys, and then grant full permissions to the SQL Server service account.
According to the CTE documentation, Common Table Expression is a temporary result set or a table in which we can do CREATE, UPDATE, DELETE but only within that scope. That is, if we create the CTE in a Stored Procedure, we can't use it in another Stored Procedure.
Although I do not understand why it would react different in 2012, I do wonder if you could work around it by using the EXECUTE AS <certificate_logon>
clause in the CREATE PROCEDURE
.
In theory this should be equivalent as to your solution as the default isEXECUTE AS owner
, but maybe it reacts slightly different ??? Worth a try I think.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With