Solved

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

Posted on 2013-11-26
18
386 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
0
Comment
Question by:Dennis_Gundersen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
18 Comments
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39678258
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.
0
 

Author Comment

by:Dennis_Gundersen
ID: 39678293
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
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39678355
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
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:Dennis_Gundersen
ID: 39678417
Excellent, thanks!

Re
D
0
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 500 total points
ID: 39678423
Surely!  So logins are security principals for the SQL Server instance as a whole.  Users are security principals for the databases themselves.  A user must be mapped to a login, otherwise they can't login.  However, the only permissions they need are the public permissions with the ability to login.  From that point they may be able to see the list of databases, but they would only be able to enumerate those databases to which they have a user account with associated permissions.  If that is the case, you would need to DENY the VIEW ANY DATABASE permission to prevent them from seeing the databases they do not have access to.  Does that help a little?

As for the logging in via SSMS, the only ways I know of to do that are with a trigger off a server side trace, which, quite frankly, bothers me in a production environment, but here is the material for you anyway: http://www.erichumphrey.com/2011/06/prevent-sql-logins-from-using-ssms/
0
 

Author Comment

by:Dennis_Gundersen
ID: 39678444
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
0
 

Author Comment

by:Dennis_Gundersen
ID: 39678478
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
0
 

Author Comment

by:Dennis_Gundersen
ID: 39678512
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
0
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39678523
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 ?
0
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39678525
Good to hear!
0
 

Author Comment

by:Dennis_Gundersen
ID: 39678591
Securables: View any database, Grantor: sa, Deny

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

Re
D
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39678767
i think the points should have gone to PadawanDBA
0
 

Author Comment

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

Re
D
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39678796
Ask a moderator to change it
0
 

Author Comment

by:Dennis_Gundersen
ID: 39678866
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.
0
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39678904
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.
0
 

Author Comment

by:Dennis_Gundersen
ID: 39678916
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL / Table Lock? 7 40
Load Fact table in SQL Server SSIS package 14 45
SQL Percentage Formula 7 33
Replace statements HTML with HTML IF 8 64
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question