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

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 (AT) hayes (DOT) ch> wrote