Import Data to Excel

Question

Import Data to Excel

jwellington

Ten Centuries

Points: 1062
More actions
October 25, 2009 at 12:53 am

#227418

Hi,
I would like to allow users to import specific data from SQL server express 2005 into Excel 2003. I can setup an excel template that accesses a specific view with the columns required. With this method I am concerned about the database security (ie. users viewing other table information). Is there a way to do this where users will only be able to see this view, and not import data from another table with the same connection (if they knew what they were doing)?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 1

Yep... create a new role on the database that has no privs to anything except the view and use that role to "Get External Data" in Excel.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jwellington Ten Centuries Points: 1062 More actions · Answer 2

Hi,

Thanks for your answer. I will look into Roles and implement.

Is ODBC the way to connect to import the data, or is there another more secure way?

Thanks

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 3

ODBC should be fine as long as the database user account is only a member of the new ROLE which only has SELECT permissions on the VIEW.

One way I would typically validate this is to log in as that user account with Query Analyzer. The only objects that appear in the Left Object Browser pane would be those that the account can access.