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

permissionssql serversql-server-2008

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.

Best Answer

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

Related Question