SQL Server - SQLServer 2008

Asked By bob

08-Mar-10 03:00 AM

Hi,
Is there anyway to update the permissions on SPROCS in bulk?
I restored an ex SQL Server 2000 db and the grant execute rights
did not come across.
It seems that you can only select SPROCS one at a time and modify the
permissions.
It was always thus with SQLServer 2000 but I thought things might have
improved.
thanks
Bob

09-Mar-10 01:28 AM

Hi Erland,
Thanks for your reply.
you are right, no permissions were lost.
It turned out only some of the sprocs had explicit grants, the others
I guess were implicitly relying on the login having dbo rights? The
ones I looked at were  implicit ones.
Anyway when I moved it to the new server the ones without explicit
rights failed to execute.
I went through every sproc and assigned expcit rights.
You indicate below what appears to be a one statement execute  to
grant rights to all sprocs to a user.
If I understand correctly then where do you execute this statment?
Is this literally just a straight statement executed in a Server
management window connected to the database?
Gosh that would have saved me a heap of time.

Could I ask one more thing?
Is there any way to script all the jobs that Server agent holds?
In 2000 you can select them all using standard techniques, and choose
script from the context menu. But it only scripts the top job in the
list.
So I am faced with individually scripting amd importing the jobs.
Again a tedious job.

thanks
Bob

09-Mar-10 05:55 PM

Hi Erland,
Thanks again for your help.
regards
Bob