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.

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
DaleUse sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale
Use sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas

"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
DaleUse sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale