Solved

Access Required to Import SQL Server Table

Posted on 2016-09-14
5
45 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 18

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal 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 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now