November 10, 2001 at 5:17 pm
Does any of you knowledgeable DBAs know how to grant execute permissions on a database level? To be more specific, I would like to give execute permissions to a login account for our application. I'd like to give an account permission on all user created stored procedures in a given database, but I want it to work universally on all databases, on several systems (SQL 7 and 2k). I noticed I could get a list of these stored procedures using sysobjects, but when I try to use a cursor, I can't use a string for
"grant execute
on @object_name
to account"
I must specify the object without quotes. I cannot use
"grant execute
to account"
either to give execute access to all stored procedures in the particular database. Does anyone have ideas for an easy and/or scriptable way to grant such permissions.
Thanks,
Eric
November 10, 2001 at 5:48 pm
Well, I figured it out, so I'll post the solution here if anyone wants to comment on it or use it.
declare @proc_name varchar(255)
declare procs cursor for
select name from sysobjects where xtype = 'P'
open procs
fetch next from procs into @proc_name
while @@fetch_status = 0
begin
execute('grant exec on ' + @proc_name + ' to security_account')
fetch next from procs into @proc_name
end
close procs
deallocate procs
This will give execute permissions on any stored procedure in the particular database. You may want to filter out any non-user stored procedures in the select statement.
Eric
November 10, 2001 at 6:04 pm
That's good enough, if that is what you need. You could use DMO also. There is no mechanism for granting access across databases other than explicitly doing so as you are here.
Andy
November 12, 2001 at 9:54 am
Andy is correct.
Personally, I add all security mods to the create/alter scripts.
Steve Jones
November 19, 2001 at 3:49 am
I use this one:
select 'grant exec on [' + name + '] to UserName' from sysobjects where xtype = 'P' and status > 0 order by name
This generate a list of grant exec on ...
for all stored procedures in a database.
You just have to rename UserName to the right userId.
November 20, 2001 at 7:21 pm
Right now I'm trying to implement a security policy on a system that was designed without one, which is the reason I need such a script. At any rate, thanks for the suggestions. If anything else, I can stand to learn something new from these posts.
Thanks,
Eric
November 20, 2001 at 10:08 pm
One thing: Use roles!!!!!
http://www.sqlservercentral.com/columnists/sjones/wp_userrights.asp
Steve Jones
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply