Solved

Access Required to Import SQL Server Table

Posted on 2016-09-14
5
72 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 28

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 28

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 49

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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…
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 ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

756 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