SQLTeam.com | Weblogs | Forums

To get result of store procedure into a table without declaring table structure

SQL Server 2008

I am doing this

SELECT
  *
INTO
  temp3939
FROM
  OPENROWSET(
    'SQLNCLI',
    'Server=spsrv;Trusted_Connection=yes;',
    'EXEC CMPL_otherdiff 202105'
)

but, I don't have trusted connection.
I can connect with sa user.

Can I do the above without using trusted connection.

According to the help on OPENROWSET (OPENROWSET (Transact-SQL) - SQL Server | Microsoft Docs), you can use a SQL account.

1 Like

@jeffw8713
if
Database name \172.16.3.3\pay_spin_cmpl
or spsrv\pay_spin_cmpl
Store procedure name CMPL_otherdiff (202105 as parameter)
User sa
password 'sa123*1'

Then could you please guide me how to put that in syntax.

Read the doc @jeffw8713 pointed you to

SELECT a.*
FROM OPENROWSET(''SQLNCLI',
'Server=sprv';
'admin';
'password',
Customers) AS a;

I would STRONGLY recommend against hard coding the password for ANY account, never mind the "SA" login.

Make sure the "SA" account is disabled on you're servers. You shouldn't ever be using it for anything.

Learn how to use EXECUTE AS OWNER in a stored procedure for this type of thing. All the ultimate user should need to have privs for is PUBLIC and EXEC privs on the proc.

2 Likes

Yes

Rights is the main issue.

Thanks

In general I would agree - however, when using OPENROWSET or linked servers it can become a nightmare setting up windows domain accounts across systems. You are almost guaranteed to run into an authentication issue or double-hop issue.

As for using 'sa' - absolutely correct and I always recommend using a least privileged account across linked servers. But as to whether or not you disable 'sa' - that is a huge can of worms to open up on vendor supplied/supported systems.

And yes - if it were up to me I would not allow vendors to use 'sa' or to use sysadmin rights from the application. But that is a battle I have lost too many times...