[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

Access Required to Import SQL Server Table

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
humbleamateur
Asked:
humbleamateur
3 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Section 2 from  ( What permissions do I need to run the wizard? )

https://msdn.microsoft.com/en-us/library/ms141209.aspx
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
humbleamateurAuthor Commented:
Thanks!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now