grant object permissions in stored procedure

I am lazy. I thought that I would get this out in the open from the outset.
Now, that said, I will justify it. I like to set up little routines that do
all of the things that I keep forgetting to do, such as setting the
permissions on new stored procedures that I add to a database.
I have a problem however; I cannot use the 'grant execute on myobject to
user' with variables.
Consider the following taken from my database permissions setup script ...
...
while (@.@.fetch_status = 0) begin
-- Set the owner to dbo if not already ...
if (@.objectUid <> @.dboUid)
execute sp_changeobjectowner @.objname=@.objectName,
@.newowner='dbo'
-- Grant public access permission.
grant execute on @.objectName to public
fetch next from objectNames into @.objectName, @.objectUid
end
...
This code generates an invalid syntax error on the lise 'grant execute ...'.
I have also tried creating a variable containing the command with the
variables expanded and using exec[ute] to execute the command. This also
fails as exec[ute] "Executes a scalar-valued, user-defined function, a
system procedure, a user-defined stored procedure, or an extended stored
procedure. Also supports the execution of a character string within a
Transact-SQL batch." and I read this (along with the error messages when I
tried it anyway) to mean that TSQL statements are not included which
suprises me as I am sure that I have exec[ute]d 'select ...' commands
before!
Does anybody have any suggestions as to how I can execute the grant
statement within the loop as shown above.
Any help will be gratefully accepted; I would hate to have to set the
permissions manually!"Martin Robins" <martin - robins @. ntlworld dot com> wrote in message
news:ek85i1WlDHA.2436@.TK2MSFTNGP09.phx.gbl...
> I am lazy. I thought that I would get this out in the open from the
outset.
> Now, that said, I will justify it. I like to set up little routines that
do
> all of the things that I keep forgetting to do, such as setting the
> permissions on new stored procedures that I add to a database.
> I have a problem however; I cannot use the 'grant execute on myobject to
> user' with variables.
> Consider the following taken from my database permissions setup script ...
> ...
> while (@.@.fetch_status = 0) begin
> -- Set the owner to dbo if not already ...
> if (@.objectUid <> @.dboUid)
> execute sp_changeobjectowner @.objname=@.objectName,
> @.newowner='dbo'
> -- Grant public access permission.
> grant execute on @.objectName to public
> fetch next from objectNames into @.objectName, @.objectUid
> end
> ...
> This code generates an invalid syntax error on the lise 'grant execute
...'.
> I have also tried creating a variable containing the command with the
> variables expanded and using exec[ute] to execute the command. This also
> fails as exec[ute] "Executes a scalar-valued, user-defined function, a
> system procedure, a user-defined stored procedure, or an extended stored
> procedure. Also supports the execution of a character string within a
> Transact-SQL batch." and I read this (along with the error messages when I
> tried it anyway) to mean that TSQL statements are not included which
> suprises me as I am sure that I have exec[ute]d 'select ...' commands
> before!
> Does anybody have any suggestions as to how I can execute the grant
> statement within the loop as shown above.
> Any help will be gratefully accepted; I would hate to have to set the
> permissions manually!
>
exec('grant execute on ' + @.objectName + ' to public')
It sounds like you're doing this already, so maybe it's just a typo. You
might consider writing your script like this, as it makes troubleshooting
much easier:
set @.sql = 'grant execute on ' + @.objectName + ' to public'
if @.debug = 1 print @.sql
else exec(@.sql)
Add a @.debug parameter to your procedure/script, and you can easily check
that your code is doing what you think it is.
Simon|||Thankyou Simon.
I did not have a typo as such, more a lack of knowledge.
my exec[ute] statement was:
set @.grantStatement = N'grant execute on [' + @.objectName +N'] to
[public]'
exec @.grantStatement
This was generating the error "The name 'grant execute on
[BrowseAddressesByCompany] to [public]' is not a valid identifier.", however
by putting in the brackets as shown in your example that allowed the
statement to execute.
Cheers.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f912ea1$1_2@.news.bluewin.ch...
> "Martin Robins" <martin - robins @. ntlworld dot com> wrote in message
> news:ek85i1WlDHA.2436@.TK2MSFTNGP09.phx.gbl...
> > I am lazy. I thought that I would get this out in the open from the
> outset.
> > Now, that said, I will justify it. I like to set up little routines that
> do
> > all of the things that I keep forgetting to do, such as setting the
> > permissions on new stored procedures that I add to a database.
> >
> > I have a problem however; I cannot use the 'grant execute on myobject to
> > user' with variables.
> >
> > Consider the following taken from my database permissions setup script
...
> >
> > ...
> > while (@.@.fetch_status = 0) begin
> >
> > -- Set the owner to dbo if not already ...
> > if (@.objectUid <> @.dboUid)
> > execute sp_changeobjectowner @.objname=@.objectName,
> > @.newowner='dbo'
> >
> > -- Grant public access permission.
> > grant execute on @.objectName to public
> >
> > fetch next from objectNames into @.objectName, @.objectUid
> > end
> > ...
> >
> > This code generates an invalid syntax error on the lise 'grant execute
> ...'.
> >
> > I have also tried creating a variable containing the command with the
> > variables expanded and using exec[ute] to execute the command. This also
> > fails as exec[ute] "Executes a scalar-valued, user-defined function, a
> > system procedure, a user-defined stored procedure, or an extended stored
> > procedure. Also supports the execution of a character string within a
> > Transact-SQL batch." and I read this (along with the error messages when
I
> > tried it anyway) to mean that TSQL statements are not included which
> > suprises me as I am sure that I have exec[ute]d 'select ...' commands
> > before!
> >
> > Does anybody have any suggestions as to how I can execute the grant
> > statement within the loop as shown above.
> >
> > Any help will be gratefully accepted; I would hate to have to set the
> > permissions manually!
> >
> >
> exec('grant execute on ' + @.objectName + ' to public')
> It sounds like you're doing this already, so maybe it's just a typo. You
> might consider writing your script like this, as it makes troubleshooting
> much easier:
> set @.sql = 'grant execute on ' + @.objectName + ' to public'
> if @.debug = 1 print @.sql
> else exec(@.sql)
> Add a @.debug parameter to your procedure/script, and you can easily check
> that your code is doing what you think it is.
> Simon
>