Generating scripts in SQL 2005

In SQL 2000 EM you were able to generate a script that only contained GRANT statements, (by unchecking the CREATE & DROP commands on the 2nd tab, and selecting Object Permissions on the 3rd tab). From what I can see in SQL 2005 MS you can't do this.

All that you can seem to do is generate a sciprt containing all the CREATE statements with the GRANT statement(s) after each object.

This is a bit of a pain as I want to script all the object permissions, without having to wade through hundreds of CREATE TABLE and CREATE PROCEDURE statements.

How can get just a script of GRANT statements?

Thanks.

It is not possible to generate scripts for object permissions through Management Studio like you could in Enterprise Manager. However, you can write a small SMO program to get the information the you need. This example should get you started on how to generate GRANT scripts for stored procedures.

ObjectPermissionInfo[] a = new ObjectPermissionInfo[5];

Database db = srv.Databases["pubs"];

foreach (StoredProcedure sp in db.StoredProcedures)

{

a = sp.EnumObjectPermissions();

foreach (ObjectPermissionInfo op in a)

{

Console.WriteLine("GRANT " + op.PermissionType.ToString() + " ON " + op.ObjectName.ToString() + " TO " + op.Grantee.ToString());

}

}

~Peter

|||Thanks for answering, I'll try what you suggest.

Generate .sql ?

Hi!

When MS published starter kits there were files .sql in App_Data. This files contained some sample data for a DB. How to create such files when I have database with data ?

Jarod

The quick answer is you can't and the reason is when there is data you need to Backup and Restore because it moves everything including permissions and data in the database, the other option is to Detach and Attach with code, the last option use DTS/SSIS to move the database because it is a data transfer and cleaning tool. I have a faq of all the methods to move a database, post again if you still have question. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||I agree with Caddre. For database objects, you can only generate DDL statements for them. There is no way to generate .sql for data.