Access another database from sp

I've looked around on how to do this and haven't had any luck so far. I have a stored procedure that needs to update tables in the current database (db1) and in another database (db2) on the same server. The user executing the stored procedure on db1 does not have permissions to access db2, but does know the username, password, and connection string of db2. Is there any way to create a connection to db2 in the stored procedure using the credentials I have? I've seen sp_addlinkedserver, but I don't think I need to use that because that database is on the same server? I just can't figure out how to 'switch' users to update the other database from the one stored procedure.

If I haven't made myself clear just let me know and I'll do my best to give a better explanation. Any help would be greatly appreciated.

Option 1:

Create a credential which has access on both db1 & db2 database. It will tune your query performance, bcs it is a direct access & you need not to stick with Ad-Hoc Remote quires.

Option 2:

Using Ad-Hoc remote quires using OPENROWSET or OPENDATASOURCE.

|||

As Mani indicates, the user will, in some way, require permission to access db2. However, it may not be necessary to provide the user direct access permissions. You may wish to explore the new 'EXECUTE AS' functionality (SQL 2005).

Refer to Books Online, Topic: "EXECUTE AS'

|||

Arnie,

I am not sure EXECUTE AS will help here.

As per my understanding, EXECUTE AS will affect the entire scope, if we need to change the context then we have to use the Execute AS & revert.

I created 2 users,

user1 only have access on db1

user2 only have access on db2

Without direct access permission on both db, user1 can't able to access the db2 rite? (even with execute as). If it allows then i can create any sp with Execute As target db user name, and i can access all the data.

Am i missing something here?

|||

Mani,

As in the Example , in Books Online, Topic: 'EXECUTE AS (Transact-SQL)', inside the stored procedure, the security context can be changed, the UPDATE statement can be executed in db2, and then the security context can revert -leaving the entirety of the context switching 'hidden' from the application users' view.

And, if necessary, the sproc can be encrypted locally to obfuscate the context switching from developers.

Absolutely no luck connecting

Hello all.
I have one laptop and one workstation.
Both are running:
- Windows XP Professional SP2
- Microsoft SQL Server 2005 SP2 (SQLServer2005SP2-KB921896-x86-ENU)
- No Domain. Workgroup configuration. Same username and password on
both systems.
- Each SQL instance is named.
- TCP and NP remote connections enabled via Surface Area Configuration
Tool.
- SQL Server Browser Service is running.
- No firewall, Windows or otherwise on either machine.
- File and Print Sharing are working normally between machines.
- Windows NT and SQL Server events logs are error-free.
- NETSTAT -aod shows SQL Server Browser listening on Port 1434 (UDP).
- SQL Server Service configured to use dynamic port numbers.
Using SQL Server Managment Studio to connect from one machine to the
other, e.g. Laptop to Workstation, Workstation to Laptop.
In all cases, Error Message is:
TITLE: Connect to Server
--
Cannot connect to DELL-D800\SQL2K5.
--
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?Prod...-1&LinkId=20476
--
BUTTONS:
OK
--
I've tried Instance names, IP/Port combinations, e.g.
192.168.13.8,1080, Windows Authentication, SQL Server Authentication;
nothing works.
Anyone have any ideas?More info:
- Instances are not hidden.
- DNS resolves names completely. No other TCP/IP-related errors.
- Local connections are working.
Thanks all.

Absolutely no luck connecting

Hello all.
I have one laptop and one workstation.
Both are running:
- Windows XP Professional SP2
- Microsoft SQL Server 2005 SP2 (SQLServer2005SP2-KB921896-x86-ENU)
- No Domain. Workgroup configuration. Same username and password on
both systems.
- Each SQL instance is named.
- TCP and NP remote connections enabled via Surface Area Configuration
Tool.
- SQL Server Browser Service is running.
- No firewall, Windows or otherwise on either machine.
- File and Print Sharing are working normally between machines.
- Windows NT and SQL Server events logs are error-free.
- NETSTAT -aod shows SQL Server Browser listening on Port 1434 (UDP).
- SQL Server Service configured to use dynamic port numbers.
Using SQL Server Managment Studio to connect from one machine to the
other, e.g. Laptop to Workstation, Workstation to Laptop.
In all cases, Error Message is:
TITLE: Connect to Server
Cannot connect to DELL-D800\SQL2K5.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLS erver&EvtID=-1&LinkId=20476[/url]
BUTTONS:
OK
I've tried Instance names, IP/Port combinations, e.g.
192.168.13.8,1080, Windows Authentication, SQL Server Authentication;
nothing works.
Anyone have any ideas?
More info:
- Instances are not hidden.
- DNS resolves names completely. No other TCP/IP-related errors.
- Local connections are working.
Thanks all.
|||I am having the same problem connecting to a Vista Machine running SQL Express 2005 SP2 from an XP laptop. Like you, I have checked all the configuration settings and they seem correct. Remote connections are enabled.
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com