I am logged into SQL Server Management Studio with an account that has access to multiple databases, but not DB_123. As shown in the picture below, clicking to expand the "Databases" folder in the Object Explorer results in an error message.

enter image description here

I'm trying to follow instructions here and here to right-click on my database and export it that way. However, I cannot right-click on it because it won't show up in the Object Explorer. Yet I do have permission to query the database.

Is there any way to get the database(s) to appear in the Object Explorer or to otherwise dump the database(s) without using the Object Explorer?

  • For that you need the privileges. Are you Administrator in the system. or which user tries to access the database? – nbk Dec 8 '20 at 22:28
  • The logged-in user has access to query the database DB_D002, but not DB_123. This user should not be able to access DB_123, but should still be able to get data from and get the schema for DB_D002. – nullromo Dec 8 '20 at 23:08
  • @nullromo Do you want the entire database (data and schema) or just the schema? – J.D. Dec 8 '20 at 23:48

One thing you can do, if you need both the data and schema, is script out a backup of the database instead of trying to use the UI in SSMS. You can find how to do that in the MS Docs: Create a Full Database Backup - Using Transact-SQL.

This is the T-SQL example from the above docs using your database name, for reference:

USE DB_D002;
TO DISK = 'c:\backup folder\DB_D002.bak'
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of DB_D002';

Note if you run into the "Operating system error 5(Access is denied.)" error, then likely the file path you provided to the "TO DISK" option in the query does not provide write access to the SQL Server Service account (in Windows), and you can provide a different path that does or follow this StackOverflow on how to fix it for the specified path.

  • 1
    Thank you. This made me realize that "dumping the database to a file" and "creating a backup" are essentially the same thing. I was not looking for how to make a backup when googling. Unfortunately, the user does not have permission to back up the database (I guess that's a separate access control from reading the database) so I found a workaround (see my answer). – nullromo Dec 9 '20 at 18:13
  • 1
    No prob! Yea the user needs the db_backupoperator role for the database you want them to be able to backup. – J.D. Dec 9 '20 at 18:19

I came up with a workaround which involves using the SQL CLI (sqlcmd) and writing each table to a file.

# prompt for password
echo "Enter password for ****** user:"
read -s password
echo ""

do_sql="sqlcmd -S ****** -d DB_D002 -U ****** -P ${password} -s ',' -k 1"

# create workspace directory
echo "Creating workspace directory..."
mkdir -p $FOLDER
echo "Done"

# get all table names from the database
echo "Getting all table names..."
$do_sql -h -1 -o ${FOLDER}/all_table_names.csv < ${FOLDER}/get_all_table_names.sql
rm ${FOLDER}/get_all_table_names.sql
sed -i "/Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C./d" ${FOLDER}/all_table_names.csv
sed -i "/^\s*$/d" ${FOLDER}/all_table_names.csv
echo "Done"

# make a query file for each table
echo "Creating query files..."
while read line; do
    echo -e "SET NOCOUNT ON;\nSELECT '${line}' AS tableName, * FROM ${line};\nGO" > ${FOLDER}/${line}.sql
done < ${FOLDER}/all_table_names.csv
TOTAL=$(wc -l < ${FOLDER}/all_table_names.csv)
rm ${FOLDER}/all_table_names.csv
echo "Done"

# execute all the queries and replace the query files with the result files
echo "Executing queries..."
echo ""
for filename in ${FOLDER}/*.sql; do
    echo -e "\e[1A\e[K${INDEX} of $TOTAL..."
    BASE=$(basename -s .sql $filename)
    $do_sql -o ${FOLDER}/${BASE}.csv < $filename
    rm $filename
echo "Done"

This creates a folder, dump_results, which contains a file for each table in DB_D002 named according to the table name and containing the contents of the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.