Posted by abu hisham on 10/01/07 15:06

On Sep 20, 10:26 pm, "alvinstraigh...@hotmail.com"
<alvinstraigh...@hotmail.com> wrote:
> On Sep 20, 4:11 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
>
>
>
>
> > alvinstraigh...@hotmail.com (alvinstraigh...@hotmail.com) writes:
> > > I'm pretty new to SQL configuration, and I need to give EXECUTE
> > > persmissions for one of the SQL user roles. I am running SQL 2005
> > > Management Studio Express - free version. I found the list of my
> > > stored procedures, but I can not locate any permissions screen. Can
> > > someone help point me in the right direction? Thanks!
>
> > If you want to use the GUI, make sure that you have SP2. I think that
> > alternative was missing in RTM and SP1.
>
> > Then again, in the long run you are better of using GRANT commands.
>
> > --
> > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> Ahh, I see now why I was lost. I right click on the SP, and there is
> no option for Properties. Yet, I can set permissions on tables. How
> stupid. You mention SP2. How can I tell which service pack I am
> running? I went to Help - About and it shows:
>
> Microsoft SQL Server Management Studio Express Version 9.00.2047.00- Hide quoted text -
>
> - Show quoted text -

You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')

/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')

[Back to original message]