Re: OBJECT Level Permissions

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 03/02/04 

Date: Tue, 2 Mar 2004 14:14:39 -0600

Though you should use the information_schema views, rather than the
sysobjects table :) 

-- 
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management
Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are
interested in consulting services.  All other replies will be ignored :)
"Brian Moran" <brian@solidqualitylearning.com> wrote in message
news:%231rEq6FAEHA.3048@tk2msftngp13.phx.gbl...
> There isn't a generic collection object that would allow you to GRANT EXEC
> on all procs in a single statement. Ultimately, it will be one stmt per
> proc.
>
> However you could build a cursor that looks at sysobjects and loops though
> the procedures...
>
> You could also do something like this to help you build the script in the
> first place...
>
> select 'grant exec on ' + name +  ' to public' from sysobjects where type
=
> 'P'
>
> -- 
>
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
>
> "bokey" <mike_bohlken@hotmail.com> wrote in message
> news:%23eVP8uFAEHA.3456@TK2MSFTNGP09.phx.gbl...
> > I know how to GRANT permissions to one object at at time.
> >
> > But...  What is the best way to GRANT EXEC permissions on "all" my
stored
> > procs in the fewest lines possible?
> >
> > Thanks
> >
> >
>
>

Relevant Pages

  • Re: GRANT - Execute SP
    ... from sysobjects where type = 'P' ... Jasper Smith (SQL Server MVP) ... grant exec to each sp individually, ...
    (microsoft.public.sqlserver.security)
  • MS SQL Server - a plethora of limitations...
    ... there are limitations in SQL Server, ... still cannot produce a decent version of MS SQL Server, ... No create or replace for functions / procs. ... > RefID1 int not null foreign key references ForKey ...
    (microsoft.public.sqlserver.programming)
  • Re: MS SQL Server - a plethora of limitations...
    ... SQL Server Programmer ... No create or replace for functions / procs. ... > 21 No boolean type for use in stored procs / funcs. ... > RefID1 int not null foreign key references ForKeyon delete ...
    (microsoft.public.sqlserver.programming)
  • Re: turn off Dist agent after pending transactions are committed
    ... Looking for a SQL Server replication book? ... >>Hilary Cotter ... >>> I do change the procs @ this point, ... Even if it means the Dist Agent blows ...
    (microsoft.public.sqlserver.replication)
  • Re: Table Creation DateTime
    ... If you are using proper permissions in place then from the sysobjects table ... In my production system I can see a user table Create Date got ... > does any of the SQL server operation does change the Create Date of the ... Books Online for SQL Server SP3 at ...
    (microsoft.public.sqlserver.server)
Loading