Management studio: how to script object with GRANT statements

30 Jan 2006 1:30 PM

Jan DHondt

In 2000 query analyzer you could browse objects (tables, views, sprocs) right
click on object en script aboject to new window. In the same right-click
dropdown menu there was a 'scripting options' item, where you could check the
option to also script the permissions statement (GRANT).
I can script objects in Management Studio but I can nowhere find how to turn
on the option to script the GRANT statements as well.

Thanks in advance.

--
Jan D''Hondt
Jade bvba
Belgium
Database and .NET development

<Toto, I''ve got a feeling we''re not in Kansas anymore.>

30 Jan 2006 11:30 PM

Erland Sommarskog

Jan DHondt (JanDHo***@discussions.microsoft.com) writes:

> In 2000 query analyzer you could browse objects (tables, views, sprocs)
> right click on object en script aboject to new window. In the same
> right-click dropdown menu there was a 'scripting options' item, where
> you could check the option to also script the permissions statement
> (GRANT). I can script objects in Management Studio but I can nowhere
> find how to turn on the option to script the GRANT statements as well.

One more feature from the old tools lost. Well, you can right-click
a database, select All Tasks, and a bit down that menu Generate Scripts.
This will bring up a wizard where you can script a couple of objects,
and there you can also choose a among a couple of scripting options.
It's not relly the same thing as setting up a default.

I would encourage you to submit suggestion for this on
http://lab.msdn.microsoft.com/ProductFeedback/ (or search for similar
and vote on one).

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

1 Feb 2006 1:21 PM

Jan DHondt

Erland,

Thank you for the information.

No it is certainly not like the old tool. As a developer I used this feature
most often when the user asked for a change in an existing application.
when this change requires a new object in the database, I would first create
the new object in a testdatabase and script the objects I create to .sql
files. When the change is ready for deployment I would then connect to the
production database and execute my scripts. With the script permissions
option, I was also sure the objects were granted the same permissions in the
production database as in the test database.
The possibility that you are pointing out is better suited for new
installations, rather than making changes in existing databases.

--
Jan D''Hondt
Jade bvba
Belgium
Database and .NET development

<Toto, I''ve got a feeling we''re not in Kansas anymore.>

Show quote

"Erland Sommarskog" wrote:

> Jan DHondt (JanDHo***@discussions.microsoft.com) writes:
> > In 2000 query analyzer you could browse objects (tables, views, sprocs)
> > right click on object en script aboject to new window. In the same
> > right-click dropdown menu there was a 'scripting options' item, where
> > you could check the option to also script the permissions statement
> > (GRANT). I can script objects in Management Studio but I can nowhere
> > find how to turn on the option to script the GRANT statements as well.
>
> One more feature from the old tools lost. Well, you can right-click
> a database, select All Tasks, and a bit down that menu Generate Scripts.
> This will bring up a wizard where you can script a couple of objects,
> and there you can also choose a among a couple of scripting options.
> It's not relly the same thing as setting up a default.
>
> I would encourage you to submit suggestion for this on
> http://lab.msdn.microsoft.com/ProductFeedback/ (or search for similar
> and vote on one).
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>