Sql-server – Can’t Dump Database Because MS SQL Server Management Studio Won’t Show Any Databases If 1 Is Permission Denied

dumpsql serverssms

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?

Best Answer

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;
GO
BACKUP DATABASE DB_D002
TO DISK = 'c:\backup folder\DB_D002.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of DB_D002';
GO

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.

Related Question