grant object permissions in stored procedure - 10-18-2003 , 06:45 AM
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,
-- Grant public access permission.
grant execute on @objectName to public
fetch next from objectNames into @objectName, @objectUid
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
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
Re: grant object permissions in stored procedure - 10-18-2003 , 07:14 AM
"Martin Robins" <martin - robins @ ntlworld dot com> wrote
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
set @sql = 'grant execute on ' + @objectName + ' to public'
if @debug = 1 print @sql
Add a @debug parameter to your procedure/script, and you can easily check
that your code is doing what you think it is.
Re: grant object permissions in stored procedure - 10-18-2003 , 04:23 PM
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
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.
"Simon Hayes" <sql (AT) hayes (DOT) ch> wrote