Giving Special Permissions to a Stored Procedure in SQL Server

Question

I have a restricted database (that contains confidential data) called database B with only a couple of users with access to it. However, I need some select data from a couple of tables within B to be available to the users who request it. The users requesting the data are not added as database users to database B as they are not allowed to have access. These users, however, have access to database A (they are added as DB users of DB A). Is there a way to give a stored procedure access to the confidential database (B) so that any user running the SP could get the data they are requesting? If so, how? I’ve studied that this is possible, but without mention of how.

NOTE: it is NOT an option to move the needed data from Database B to A through IS package (given real-time access is needed). I’ve thought of using views too (but again, how? If you don’t have access to the base tables, you won’t have access to the data through the view either).

Answer 1

This is fairly easy to accomplish (including handling multiple databases and Dynamic SQL) without any Impersonation (IMPERSONATE permission), cross-database ownership chaining (Server / Instance or Database setting), or TRUSTWORTHY (Database setting). What you need to do, generally speaking, is:

1. Create a Certificate in Database A
2. Sign Stored Procedure in Database A using ADD SIGNATURE
3. Create the same Certificate in Database B
4. Create a User in Database B based on that Certificate
5. Assign the new User the permissions needed to access the desired tables

Example:

CLEANUP

USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseA')
BEGIN
    PRINT 'Dropping [DatabaseA] DB...';
    ALTER DATABASE [DatabaseA] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseA] SET ONLINE;
    DROP DATABASE [DatabaseA];
END;

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseB')
BEGIN
    PRINT 'Dropping [DatabaseB] DB...';
    ALTER DATABASE [DatabaseB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseB] SET ONLINE;
    DROP DATABASE [DatabaseB];
END;

IF (SUSER_ID(N'JohnnyLunchbucket') IS NOT NULL)
BEGIN
  PRINT 'Dropping [JohnnyLunchbucket] Login...';
  DROP LOGIN [JohnnyLunchbucket];
END;

IF (OBJECT_ID(N'tempdb..#CertInfo') IS NOT NULL)
BEGIN
  PRINT 'Dropping [#CertInfo] Temp Table...';
  DROP TABLE #CertInfo;
END;

SETUP

USE [master];

EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [DatabaseA] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [DatabaseB] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO

ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseA] SET TRUSTWORTHY OFF;

ALTER DATABASE [DatabaseB] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseB] SET TRUSTWORTHY OFF;
GO

CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'OhSoSecure;)';


USE [DatabaseA];

CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];
GO

CREATE PROCEDURE dbo.RunReport
AS
SET NOCOUNT ON;

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
GO

GRANT EXECUTE ON dbo.RunReport TO [JohnnyLunchbucket];

CREATE CERTIFICATE [PermissionsCert]
  AUTHORIZATION [dbo]
  ENCRYPTION BY PASSWORD = 'WeakPassword'
  WITH SUBJECT = 'Used to test granting permissions to code',
  EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE TO [dbo].[RunReport]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';

SELECT CERTENCODED(CERT_ID(N'PermissionsCert')) AS [PublicKey],
       CERTPRIVATEKEY(CERT_ID(N'PermissionsCert'), 'OtherPassword', 'WeakPassword')
                 AS [PrivateKey]
INTO   #CertInfo;
GO

USE [DatabaseB];

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'CREATE CERTIFICATE [PermissionsCert] AUTHORIZATION [dbo] FROM BINARY = '
               + CONVERT(NVARCHAR(MAX), [PublicKey], 1)
               + N' WITH PRIVATE KEY (BINARY = '
               + CONVERT(NVARCHAR(MAX), [PrivateKey], 1)
               + N', DECRYPTION BY PASSWORD = N''OtherPassword'''
               + N', ENCRYPTION BY PASSWORD = ''WeakPassword'');'
FROM   #CertInfo;

PRINT @SQL;
EXEC (@SQL);

CREATE USER [PermissionsUser] FROM CERTIFICATE [PermissionsCert];

CREATE TABLE dbo.[RestrictedTable]
(
  [ID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_RestrictedTable] PRIMARY KEY,
  [Other] VARCHAR(50)
);

GRANT SELECT ON [dbo].[RestrictedTable] TO [PermissionsUser];

INSERT INTO dbo.[RestrictedTable] ([Other]) VALUES ('Ta da!');
GO

TEST

-- Quick test to show that [PermissionsUser] cannot be Impersonated:
USE [DatabaseB];
EXECUTE AS USER = 'PermissionsUser';
/*
Msg 15517, Level 16, State 1, Line 123
Cannot execute as the database principal because the principal "PermissionsUser" does not
exist, this type of principal cannot be impersonated, or you do not have permission.
*/


-- Main test:
USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
/*
Msg 916, Level 14, State 1, Line 144
The server principal "JohnnyLunchbucket" is not able to access the database "DatabaseB"
under the current security context.
*/

EXEC [dbo].[RunReport]; -- SUCCESS!!!
-- 1    Ta da!


REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

For more info on Impersonation vs Module Signing you can review this other answer of mine (and the answer linked in it):

SQL Server Impersonation is just NOT working

Answer 2

Basically, you need to create a process outside of the secured DB that will allow a non-authorized user to peek into the data in a very limited way. The simplest way to do this is to create a stored procedure with an EXECUTE AS clause, giving the code inside the procedure the needed permissions to expose the selected data, but otherwise keeping the permissions locked down. This would look something like:

Use AccessableDB
GO
CREATE PROCEDURE GetTheStuff (Param1 int, Param2 bit = 1)
WITH EXECUTE AS 'HighPermissionsUserInOtherDB'
AS
BEGIN 
    -- Code referencing InaccessableDB
END

Answer 3

Create a user account on the server. Create that user with no permissions in the DB you want them to have limited access for. They will not have access to anything in that database currently.

Create a view to the table you want with the limited columns and permissions required. Then pair that user to access the view.

Now in the DB you want them to use, you can create a proc there that references the view you wanted while limiting their permissions and not allowing them access to the underlying table so they can now do a higher level of data aggregation.

This does violate the not being able to add them request of your question, but this is a good work around while providing them with no actual access to anything in this database besides that one view. They don't need access to the table as your problem states but rather the database. The error you will see is actually:

Msg 916, Level 14, State 1, Procedure NAME, Line 11 The server principal "USER" is not able to access the database "NAME" under the current security context

Does this idea solve the problem? Example below.

CREATE DATABASE TestA; 
GO

CREATE DATABASE TestB;
GO

USE [master];
GO

CREATE LOGIN TestUser WITH PASSWORD = 'Password', CHECK_POLICY = OFF;
GO

CREATE TABLE TestA.dbo.t1(id INT);
GO

USE TestA; 
go 

CREATE VIEW dbo.ViewA
AS
  SELECT id FROM dbo.t1;
GO

CREATE USER TestUser FROM LOGIN TestUser;
GO

GRANT SELECT ON TestA.dbo.ViewA TO TestUser;
GO

USE TestB;
GO

CREATE USER TestUser FROM LOGIN TestUser;
GO

CREATE PROCEDURE dbo.TestProcedure 
AS
  SELECT id FROM TestA.dbo.ViewA;
GO

GRANT EXECUTE ON TestB.dbo.TestProcedure TO TestUser;
GO