1

Is it possible to export a SQL Server database (data, structures) without its users ?

If yes, is it possible using SSMS ?

Spilarix
  • 125
  • 5

2 Answers2

6

There is a way to get the result you're after but it's not a backup and should not be considered as such (see gbn's answer to understand why).

This is a handy tool for generating scripts that you might use in an application installer and to prime your application's database with objects such as stored procedures, views reference tables and data. But it's not to be used as a mission critical backup.

Tasks -> Generate Scripts provides the capability to script your database and data and choose which objects to ignore (for example users):

enter image description here

On the second page you can choose what to script:

enter image description here

Just make use "Users" is not selected.

On the next page select the Advanced button to open the Advanced Scripting Options window:

enter image description here

The Advanced Scripting Options window allows you to pick and choose what to script and how to script it. If you want to script the data as well as the schema then choose Schema and Data in the "Types of data to script" option:

enter image description here

Again and just to re-iterate, this is not considered a reliable or sanctioned method to backup a database.

Community
  • 1
Kev
  • 7,877
  • 18
  • 81
  • 108
  • Note: this isn't a coherent, restorable, point in time snapshot of the database. – gbn Oct 10 '11 at 08:54
  • @gbn - OP just clarified that this is just an export and not treating as backup. – Kev Oct 11 '11 at 01:27
2

No. Users can:

  • have permissions
  • be memebers of roles
  • own schemas and objects

If users were stripped out you break the database

If you want to export all data and scripts then you can skip users that way.
But this isn't a backup

gbn
  • 6,079
  • 1
  • 18
  • 21