I posted this thread in security also. But cross posting here with hope of getting a quick answer.

I am trying to generate the create database script using SMO. The issue is the user account which connects to SQL server is not SA, SMO fails to generate the "Create Database" script with error User ZZZ\User1 does not have permission to run DBCC SHOWFILESTATS.

Is it possible to generate a "Create database" script using SMO when you are connecting to the SQL server using NON-ADMIN account

The user login is a part of db_Datareader role on the database. I tried following grant permission to the concerned account at server level

Grant view any database, grant view any definition and grant view server state. Still it fails.

Any idea

Re: Can a non-admin user generate "Create database" script using SMO?

Mitesh Shah923

One thing you can try is using the Server.ConnectionContext.ConnectionString property to specify the connection string the way we do it in applications and give the sa user id and password.

Other was is to grant dbwriter along with modify schema permission to the user.

Re: Can a non-admin user generate "Create database" script using SMO?


Thanks Mitesh for your help. The user who connect to the server will be using it ONLY for generating the script. He is not going to create the database on the same server. Once the script is generated, the database will be created using the earlier generated script on other server, where the user has proper permission. There is no way user can be made SA, ONLY for generating the script (and that too only for CREATE DATABASE script). There is no chance of getting sa password on the first server from where user is trying to generate the script, as it is a production server.