We help IT Professionals succeed at work.

How to define a user in MS SQL Server 2012 that can import data into Excel, but nothing more

441 Views
Last Modified: 2013-11-29
I need to setup a user in SQL Server 2012 that can download data into Excel 2013 PowerPivot. It seems that it's not possible to completely hide the connection string in Excel, so it's important that the user has the absolute minimum of rights on the server instance and database as the Excel workbook will be shared. Would appreciate any good suggestions.

TIA
Dennis
Comment
Watch Question

PadawanDBAOperational DBA

Commented:
Would read only on the database that they are pivoting from count as absolute minimum rights?  They would be able to log in to the instance and read from the database you give them access to.  If yes, you would assign their login to the db_datareader role (in user mapping section of the login properties window) for that specific database.

Edit: If this is something that you anticipate needing to do again in the future, I would recommend an AD security group be granted these permissions, that way you only need to manage group membership from AD.

Author

Commented:
This Excel sheet has to be shared on a website, so AD isn't an option. I've already given the user db_datareader and SELECT right, but I really don't like the idea of unknown people being able to log on to the instance and view other databases, so I have was hoping reading data and logging in using SSMS could be split (don't have a firm understanding of the difference between login and user I'm afraid).

Re
D

Commented:
As PadawanDBA explained in his answer

you would assign their login to the db_datareader role (in user mapping section of the login properties window) for that specific database.

Permissions are granted for that database only, your users wouldnt be able to access other databases

Author

Commented:
Excellent, thanks!

Re
D
Operational DBA
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
With the deny view database it should work out well. These people are my friends, but I use other databases too that they don't need to see, and an Excel sheet can show up in a Google search, so better safe than sorry.

Re
D

Author

Commented:
I'm still missing something. I added a new user with
USE [master]
GO
CREATE LOGIN [ReadOnlyUser] WITH PASSWORD=N'Admin1234', DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Test]
GO
CREATE USER [ReadOnlyUser] FOR LOGIN [ReadOnlyUser]
GO
USE [Test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ReadOnlyUser]
GO
DENY VIEW ANY DATABASE TO [ReadOnlyUser]
GO

But when I log in with that user, I can't see the Test database.

Re
D

Author

Commented:
Never mind, I just forgot to include
use [Test]
GO
GRANT SELECT TO [ReadOnlyUser]
GO

It works great now. Only the Test database shows up in Excel and and nothing shows up in SSMS, but the user can still query that one database.

Re
D
PadawanDBAOperational DBA

Commented:
Can you take a quick peek at the login for ReadOnlyUser ( Security > Logins > Right click 'ReadOnlyUser' > Properties > Securables > Permissions for <servername here> section) and tell me what it says next to View any database?  Also check in the user mapping tab, what are the permissions for the test database ?
PadawanDBAOperational DBA

Commented:
Good to hear!

Author

Commented:
Securables: View any database, Grantor: sa, Deny

User Mapping: Database: Test, User: ReadOnlyUser, Default Schema: dbo, Membership: db_datareader and public

Re
D

Commented:
i think the points should have gone to PadawanDBA

Author

Commented:
Oops, didn't notice that I clicked the wrong link. I agree, but don't know how to change this.

Re
D

Commented:
Ask a moderator to change it

Author

Commented:
Seems I can't add a request attention call as PadawanDBA has already done so.

Moderators: I meant to give the points to PadawanDBA, but I clicked the wrong acceptance link by mistake. Sorry.
PadawanDBAOperational DBA

Commented:
I mentioned it in the request, but I am perfectly fine with a point split as QuinnDex clarified for you in a timely manner.  I'm just happy we were able to help you get things setup in a manner that works for your requirements.

Author

Commented:
No, you deserve the points, I was just a bit too quick and clicked the first green link as I scrolled my way up the page to accept the solution.

Re
D

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.