Solved

Access Required to Import SQL Server Table

Posted on 2016-09-14
5
59 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
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 47

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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