Select data from another database instance on the same server in sql server

Question

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]

This query is part of a stored procedure based on ReadAssociate database. 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.

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.

That viewassociate is a sql login which is db_owner of ViewAssociate database.

Answer 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

Answer 2

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.