2

I have a query shown below

select count(*) as Count, datepart(yyyy, [LogDate]) as [Year]
from ViewAssociate..Auth_Log 
where ActionCode = 12
group by datepart(yyyy, [LogDate])
order by [Year]

Now this query is part of a Stored Proc which is based on ReadAssociate Database and it's trying to get data from ViewAssociate db. The user trying to run the stored proc doesn't have access to the ViewAssociate db so the query throws an error. My question is can I modify the query somehow and provide it a username password to run so I can get data from the ViewAssociate Db. Someone suggested Execute As but that doesn't seem to work.

Thanks

  • what happened when you used execute as? did the user you were executing as have permission to ViewAssociate? – scsimon Feb 5 at 20:58
  • execute as login = 'viewassociate' I get this error Cannot execute as the server principal because the principal "viewassociate" does not exist, this type of principal cannot be impersonated, or you do not have permission. – SP1 Feb 5 at 21:00
  • That viewassociate is a sql login which is db_owner of ViewAssociate database – SP1 Feb 5 at 21:02
  • Just to clarify - the ViewAssociate database is in a completely different instance to ReadAssociate? I understand that they are on the same server. – Mr.Brownstone Feb 5 at 21:39
1

If the database that you are trying to connect to is on a different instance then I would recommend setting up a linked server which you can then use to obtain the data. This will allow you to explicitly use credentials which exist on the instance you want to connect to - it also keeps you query relatively simple by only needing to adjust to using a four-part name to reference the table.

-- create a linked server
USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
     @server = '.\INSTANCE_NAME',   
     @srvproduct=N'SQL Server' ;  
GO  
-- add a login to the linked server
EXEC master.dbo.sp_addlinkedsrvlogin   
     @rmtsrvname = '.\INSTANCE_NAME',   
     @locallogin = NULL,   
     @useself = N'False',
     @rmtuser = 'user_name_from_other_instance',
     @rmtpassword = 'password_for_remote_user';  
GO 
-- now you can query the server
SELECT *
  FROM [.\INSTANCE_NAME].master.sys.databases;
GO

There are more options available for these stored procedures and you can also perform the same action using SQL Server Management Studio. I have included some links to the documentation below for you:

Linked Servers

Create Linked Servers

sp_addlinkedserver

sp_addlinkedserverlogin

1

This is a simple matter of database's, by default, being isolated from other DB's for security purposes. Prior to SQL Server 2005, this issue could be resolved by enabling the Cross-Database Ownership Chaining feature. However, that option is rather wide-open as it does not allow for tailoring who can make use of it. Starting with SQL Server 2005, Module Signing was introduced, and it provides a fine-grained security mechanism for solving this problem, and many others.

I have a few examples on here (DBA.SE) already showing how to do this, such as:

Permissions in triggers when using cross database certificates

That one involves triggers, so I simplified it for this question, as shown below:

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;

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

-- Default for both options should be OFF, but just to be sure:
ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF, TRUSTWORTHY OFF, RECOVERY SIMPLE;

ALTER DATABASE [DatabaseB] SET DB_CHAINING OFF, TRUSTWORTHY OFF, RECOVERY SIMPLE;
GO

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


USE [DatabaseA];

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

CREATE PROCEDURE dbo.SelectFromDatabaseB
AS
SET NOCOUNT ON;

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

GRANT EXECUTE ON dbo.SelectFromDatabaseB TO [JohnnyLunchbucket];
GO

USE [DatabaseB];

CREATE TABLE dbo.SomeTable (Col1 INT);
INSERT INTO dbo.SomeTable ([Col1]) VALUES (111);
GO

TEST 1 (no access)

USE [DatabaseA];

EXECUTE AS LOGIN = N'JohnnyLunchbucket';

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

EXECUTE dbo.[SelectFromDatabaseB];
/*
Msg 916, Level 14, State 1, Procedure dbo.SelectFromDatabaseB, Line XXXXX [Batch Start Line YYYYY]
The server principal "JohnnyLunchbucket" is not able to access the database "DatabaseB" under the current security context.
*/

REVERT;

ADD MODULE SIGNING

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].[SelectFromDatabaseB]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';

-- Copy Certificate (public key only) to DatabaseB
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'USE  [DatabaseB];
CREATE CERTIFICATE [PermissionsCert] AUTHORIZATION [dbo] FROM BINARY = '
  + CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'PermissionsCert')), 1)
  + N';'

EXEC (@SQL);

USE [DatabaseB];

CREATE USER [PermissionsUser] FROM CERTIFICATE [PermissionsCert];

GRANT SELECT ON [dbo].[SomeTable] TO [PermissionsUser];
GO

TEST 2 (access granted, but only via signed module)

USE [DatabaseA];

EXECUTE AS LOGIN = N'JohnnyLunchbucket';

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

EXECUTE dbo.[SelectFromDatabaseB];
-- 111

REVERT;

Only the signed module(s) have the access granted to the Logins and/or Users created from the Certificate. And users only have access to the modules that you grant them access to.

0

Based on the information provided, you're actually querying across databases, not across instances, which means the fix required is very different.

As mentioned in the comments, the simplest solution is to use EXECUTE AS to allow the readassociate user to impersonate the viewassociate user during the procedure execution:

USE [ReadAssociate]
GO
CREATE ROLE [ViewAssociateReadAccess] AUTHORIZATION [sa];
GO
GRANT IMPERSONATE ON USER::ViewAssociate TO ViewAssociateReadAccess;
GO
GRANT EXECUTE ON [dbo].[StoredProcedure] TO [ViewAssociateReadAccess];
GO
ALTER ROLE [ViewAssociateReadAccess] ADD MEMBER [ReadAssociate];
GO

The above code will add a new role to your ReadAssociate database and grant that role the permission to impersonate the ViewAssociate user and execute your stored procedure. It then adds ReadAssociate to that role. After this, ReadAssociate should be able to execute the stored procedure (with EXECUTE AS LOGIN = 'ViewAssociate' added) successfully and retrieve data from the ViewAssociate database.

Using a role for this is better, because if you need to grant this access to other users later, its a simple process of adding them to the role. In addition, any extra stored procedures you need to run can simply be added to the role's permissions and all users in the role will have that access.

  • 1
    This doesn't work, and can't, at least not without some changes, including making a huge mistake. There are 2 minor issues: A) sa is a Login, not a User, so it only works at the instance-level. Changing [sa] to [dbo]` fixes that statement. B) you need to add EXECUTE AS USER, not LOGIN to the proc. Doing those 2 things will set up the framework, but there's still the default quarantine when doing Impersonation. The huge mistake would be doing ALTER DATABASE [ReadAssociate] SET TRUSTWORTHY ON;, which does make this work, but is a security hole, hence: modulesigning.info – Solomon Rutzky Feb 6 at 22:26
  • 1
    Technically speaking, doing the following instead of enabling TRUSTWORTHY would also get this setup to work: USE [master]; GRANT IMPERSONATE ON LOGIN::[ViewAssociate] TO [ReadAssociate];. That would allow EXECUTE AS LOGIN = 'ViewAssociate'; to then allow the ReadAssociate User to access the ViewAssociate DB. However, that solution also has a very high cost: the ReadAssociate Login would be able to issue EXECUTE AS LOGIN = 'ViewAssociate'; whenever they wanted. Please see: sqlquantumleap.com/2017/12/30/… – Solomon Rutzky Feb 6 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.