Can a non-admin user generate a create database script?

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?

Grant dbo permissions.

Adamus

|||

Thanks Adamus for your reply.

I am sorry, I didn't understand what I need to do? Do I need to make the user as DBO?

The user account used for creating script will NOT get any permissions like DBO or SA. In SQL 2000, if the user is part of db_datareader dbrole then it can generate the "Create database" script without any problem.

|||

Adamus, just to test things, i made the user DBO of the database. We were able to generate "Create database" script after that.

But in our scenario we will not be able to make the user as DBO just to generate "Create database" script. Is there any other way to generate a "Create database" script using non-admin user.

|||Try with granting db_ddladmin privilege for that user.|||

Typically, you'd create a generic dbo account and validate in code. If the user is going to need to create databases, they should be an admin...again typically.

Adamus

|||

Let me explain the scenario in detail. I think, i was not clear, sorry about that.

THe user is trying to generate a create database (followed by create object) script from a server where he has limited access, as the source server is production server and also owned by other team. Once the script is created, the database and object will be created on different server where the user has SA permissions. After granting "any definition" permission the user can connect to the first server and now use sp_helpdb to see the database files and create script for all the objects in the database. Only while creating the script for "Create database"it fails.

|||

If the production server is owned by the other team, there is no magic or clever trickery that will enable the user to create databases on it. You have to understand that creating databases is an activity reserved for DBA's not end users. It was designed this way for many good reasons. You don't want to hear, "Oops I forgot to use a transaction!"

If the DBA creates a generic SQL Authentication account and your developers write a script that the end user can't change, this is how it is normally handled.

The end user is forced to use an application instead of a query analyzer and is confined to the functionality of the application.

I realize this isn't the answer you were hoping to get, but it is the reality.

Adamus

|||

I am NOT trying to create the database or for that matter any object on the production server which is owned by other team. I just want to script the database (including create database script) from that server.

The script will be used for creating database and objects on another server where we will have proper permission.

I can do this very well in SQL 2000, if the user is part of DB_DATAREADER db role. In SQL 2005, "GRANT VIEW ANY" permission will let you generate the script for all the objects in the database but not just the "Create database" script. My question is Can a non-admin generate the script (Just generate the script).

|||

Thanks Satya, I missed this reply.

I tried ddl_admin also, no success. SMO runs dbcc showfilestats to generate the "Create database" script and fails at that point, saying not enough permissions to execute dbcc. I was hoping to get some workaround like DBCC INPUTBUFFER can be executed by a non-admin user if that user has "VIEW SERVER STATE" permission.

I can view the database files using sp_helpdb and just cannot generate the "Create database" script. Not able to understand, why?

|||

SQL Server 2005 incorporated many security enhancements to prevent users from having access to information they shouldn't have. If the user in question could generate a script to create a database from an existing database (without the privileges to do it on that server), the user then has access to the underlying file system locations for the database files, and through clever use of SQL Injection techniques, can then compromise the security of your existing server.

I'm not suggesting your users would do this, I'm suggesting that with the permissions you desire that it's possible either in your shop or someone elses, and that's one of the things that Microsoft is attempting to prevent.

Hopefully that makes sense.

|||

Allen, I would respectfully disagree with you. The user who is a member of db_datareader role can see the same result by executing sp_helpdb (I granted grant view any). Also with that permission the user can see the exact information from different views (sys.MasterFiles, sys.Filegroups) from sys schema. SMO is only failing because it is using dbcc command while generating the "Create database" script. Why does SMO uses DBCC command to generate a "create database" script when the same information is available in different views? This is a bug and NOT a functionality.