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.