Solved

Access Required to Import SQL Server Table

Posted on 2016-09-14
5
82 Views
Last Modified: 2016-09-21
What is the absolute minimum access would I need to a SQL Server database in order to import a non-system table from that database?

and

What is the absolute minimum access would I need to a SQL Server database in order to import a system table from that database?


If you have to be an admin or something, is there a way to still restrict the account's access to Read or something very limited?


Thanks!
0
Comment
Question by:humbleamateur
[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
5 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 41798788
If the table is already created you would need reader on the sender side and write to the table you're importing to.

If the table doesn't exist already you definitely need at least dbo on the writer side.  I'm not sure if you could get away with read or not as you may need to get the schema.  Admins can do everything so there's no way to say you can be an admin and not have write access.
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 125 total points
ID: 41799086
If your data source requires a connection string, you can find examples on this third-party site - The Connection Strings Reference.      

What permissions do I need to run the wizard?      

To run the SQL Server Import and Export Wizard successfully, you have to have at least the following permissions. If you already work with your data source and destination, you probably have the permissions that you need.      

You need permissions to do these things      If you're using SQL Server, you need these permissions
Connect to the source and destination databases or file shares.      Server and database login rights.
Export or read data from the source database or file.      SELECT permissions on the source tables and views.
Import or write data to the destination database or file.      INSERT permissions on the destination tables.
Create the destination database or file, if applicable.      CREATE DATABASE or CREATE TABLE permissions.
Save the SSIS package created by the wizard, if applicable.      If you want to save the package to SQL Server, permissions sufficient to save the package to the msdb database.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41799087
Section 2 from  ( What permissions do I need to run the wizard? )

https://msdn.microsoft.com/en-us/library/ms141209.aspx
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 41799314
What is the absolute minimum access would I need to a SQL Server database in order to import a non-system table from that database?
SELECT permission on the table.


What is the absolute minimum access would I need to a SQL Server database in order to import a system table from that database?
Depends on the table but usually having a login with PUBLIC permission in the SQL Server instance will allows you to query most of the dmvs in the master database. For msdb database you'll need the SELECT permission on the table or view.


If you have to be an admin or something, is there a way to still restrict the account's access to Read or something very limited?
If being admin you mean having sysadmin role then there's no way you can't restrict accesses. But lower admin roles can be prevented to perform some tasks or accessing some objects by applying the DENY permission on the desired object for the desired user.
0
 

Author Closing Comment

by:humbleamateur
ID: 41808165
Thanks!
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

632 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