permission for execel to connect to SQL server

Question

permission for execel to connect to SQL server

sqlfriend

SSC Guru

Points: 52769
More actions
October 18, 2016 at 10:31 am

#314524

We have some department users want to use excel to connect to our SQl server database, this way they can get most recent data.
For now we only want them to read the data, no write back to SQL database.
What is the best way of doing that, what permissions should we give for users in db?

Viewing 7 posts - 1 through 6 (of 6 total)

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

ZZartin SSC-Dedicated Points: 30896 More actions · Answer 1

The easiest way is probably just to add them to the db_datareader group in the database. Assuming you don't need any more granular permissions.

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

yes, sometimes I use stored procedure to return result.

So for those, I give user the public role, and grant execute to the stored procedure.

Is that OK?

Thanks,

Phil Parkin SSC Guru Points: 246714 More actions · Answer 3

ZZartin (10/18/2016)
The easiest way is probably just to add them to the db_datareader group in the database. Assuming you don't need any more granular permissions.

I'd create an AD group, put the users in that and then add the group to a suitable role ... to avoid individual user management in SQL Server.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 246714 More actions · Answer 4

sqlfriends (10/18/2016)
yes, sometimes I use stored procedure to return result.
So for those, I give user the public role, and grant execute to the stored procedure.
Is that OK?
Thanks,

Not the best. Use a role and GRANT/DENY permissions to the role rather than the individual.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 5

Create a stored procedure to return what they want. Give them privs to run the stored procedure and have the stored procedure execute as owner. That way, you don't have to give them even read privs.

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

Lowell SSC Guru Points: 323504 More actions · Answer 6

Jeff Moden (10/18/2016)
Create a stored procedure to return what they want. Give them privs to run the stored procedure and have the stored procedure execute as owner. That way, you don't have to give them even read privs.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!