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

1

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.

2

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

1 Like
3

@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.

4

Read the doc @jeffw8713 pointed you to

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

1 Like
5

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 your 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
6

Yes

Rights is the main issue.

Thanks

7

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...

1 Like
8

Agreed. So what is your workaround when you need to do something similar? :wink:

9

Linked server - with security defined on the linked server to map the local account to the remote SQL account (which has minimal permissions). I would also recommend evaluating the need for that data in a 'temp' table to be called each time this code is executed.

Most likely - a standardized daily extract into a local permanent table will not only satisfy this query, but many other requests. Will allow for indexing specific to those queries instead of relying on the other systems indexes, and reduce network traffic for every time this code is called.

Of course - if the requirement is for near real-time data than a daily extract won't work. But I haven't seen too many of those requirements.

Oh - one last thing...I truly despise the overuse of linked servers and/or the misuse. Too often I see code that needs last months data embedded in multiple local procedures with very little difference in the columns or data being returned.

10

Heh... I'm not sure how we got off on such a tangent. You don't need a linked server to do this. And didn't you just raise a fuss about what a nightmare linked servers can be? :wink:

11

I didn't raise a fuss about linked servers - but did raise the point that misuse and overuse is a problem I have with linked servers. The 'fuss' about linked servers is when trying to use Windows domain accounts across linked servers.

12

So, a bit of a replay... you first said...

When I asked what you would use instead, you replied with ...

I have whiplash because it certainly seemed like you were "raising a fuss" about the use of linked servers and then you recommended a way to use them in the next post.

And, so I was confused about your stand on Linked Servers. :smiley:

Personally, I don't mind using Linked Servers to do such a thing as what the OP requested. If the server has privs to the other machine, then a stored procedure with EXECUTE AS OWNER might also do the trick without a Linked Server.

13

I don't mind using linked servers - when used appropriately. However - there is a lot of misuse and abuse. I have seen too many times a developer write a query and join to a table across a linked server - then complain about performance.

14

Ok... now that I know what you mean, I totally agree. Thanks for the feedback.