Create a stored procedure that stores data elsewhere

I have a stored procedure written on a server, but do not have permissions to save it which isn't all that bad because I need it to save the output on another server to a new, empty database. I could then save the SP on the new server. But how can I get the SP on the :"old" server to put the data on the new server? Will I need to manually build the tables or will it intuitively create them as the data gets loaded?

You should have access to the tempdb, try storing it there. Easiest is to use a temp table and then copy that to the other server. If you must pull from the other server, yry using a non-temp table name in the tempdb db.

Thanks Scott...but I don't have permissions to create the stored procedure on the original database. So now I guess my question is how can I run that SP from the Linked Server and still populate it to the new server, empty database...

If your SProc is created on, and running on, NewServer then NewServer needs to have a Linked Server to OldServer - and permission to SELECT from the Tables, on OldServer, that you need access to. Personally I doubt we would ever grant SELECT permissions for that type of activity, we would only grant EXECUTE on an SProc on OldServer :slight_smile: and if that is the case for you, too, then you are hosed - and back to square one. (We don't grant SELECT permissions on any tables in our DB PERIOD. If you need SELECT permissions on one of my tables come and ask me for that permission. You better have a good reason, but the answer is still, almost certainly, "no" :slight_smile: )

I reckon your first approach should be to get your Sproc authorised on OldServer, but if you can get a LinkedServer created on NewServer, linking to OldServer, and also get SELECT permission on the Database/Table(s) that you need you then you are good-to-go :slight_smile: - but at that point I suggest you ask the question "why" you were granted those permissions ...

I would be interested to see if other folk here would be more comfortable with granting SELECT permissions for this type of thing than I would.