Help - user permissions question

Hi,
I have a database user with read-only permissions in the database.
The user would like to be able to display a list of stored procedures
in the database. I would like to grant this user the permission to do
this, but without granting permission to *change* the sp's in any way.
Is there a way to do this?
THANKSRead only permission is enough to display the stored procedures list. Here is
the query:
/* It will list the names of all stored proc */
select name from sysobjects
where type = 'P'
order by name
/* It will list names of user stored procs only */
select name from sysobjects
where type = 'P' and status not like '-%'
order by name
Bob
"tootsuite@.gmail.com" wrote:
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||What version of SQL Server?
In 2000, you can see all objects in the database.
In 2005, you can see the objects that you have permissions to use (execute). You can grant
permission to see an object even if you cannot execute/select from etc it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||SQL 2000 or SQL 2005?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> Hi,
> I have a database user with read-only permissions in the database.
> The user would like to be able to display a list of stored procedures
> in the database. I would like to grant this user the permission to do
> this, but without granting permission to *change* the sp's in any way.
> Is there a way to do this?
> THANKS
>|||Yes, sorry forgot to mention - SQL Server 2005
I know permissions work differently on 2005 than 2000. Please tell me I
do *not* have to grant execute permission to the user for each and
every user stored procedure in the database'
Is there another way to allow the user to see?
Arnie Rowland wrote:
> SQL 2000 or SQL 2005?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > Hi,
> >
> > I have a database user with read-only permissions in the database.
> >
> > The user would like to be able to display a list of stored procedures
> > in the database. I would like to grant this user the permission to do
> > this, but without granting permission to *change* the sp's in any way.
> >
> > Is there a way to do this?
> >
> > THANKS
> >|||Yes, in SQL Server 2005 you can grant user "View Definition" permission so
that they can see the stored procedures but not alter or execute it.
For example:
GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
TO User;
GO
"tootsuite@.gmail.com" wrote:
> Yes, sorry forgot to mention - SQL Server 2005
> I know permissions work differently on 2005 than 2000. Please tell me I
> do *not* have to grant execute permission to the user for each and
> every user stored procedure in the database'
> Is there another way to allow the user to see?
>
> Arnie Rowland wrote:
> > SQL 2000 or SQL 2005?
> >
> > --
> > Arnie Rowland, Ph.D.
> > Westwood Consulting, Inc
> >
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > <tootsuite@.gmail.com> wrote in message
> > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > Hi,
> > >
> > > I have a database user with read-only permissions in the database.
> > >
> > > The user would like to be able to display a list of stored procedures
> > > in the database. I would like to grant this user the permission to do
> > > this, but without granting permission to *change* the sp's in any way.
> > >
> > > Is there a way to do this?
> > >
> > > THANKS
> > >
>|||Hi,
Thanks - do I have to run this statement for each and every stored
procedure I wish to grant user permissions on? Or is there a single
stmt I can use?
Thanks
Bob wrote:
> Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> that they can see the stored procedures but not alter or execute it.
> For example:
> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> TO User;
> GO
>
> "tootsuite@.gmail.com" wrote:
> > Yes, sorry forgot to mention - SQL Server 2005
> >
> > I know permissions work differently on 2005 than 2000. Please tell me I
> > do *not* have to grant execute permission to the user for each and
> > every user stored procedure in the database'
> >
> > Is there another way to allow the user to see?
> >
> >
> >
> > Arnie Rowland wrote:
> > > SQL 2000 or SQL 2005?
> > >
> > > --
> > > Arnie Rowland, Ph.D.
> > > Westwood Consulting, Inc
> > >
> > > Most good judgment comes from experience.
> > > Most experience comes from bad judgment.
> > > - Anonymous
> > >
> > >
> > > <tootsuite@.gmail.com> wrote in message
> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > > Hi,
> > > >
> > > > I have a database user with read-only permissions in the database.
> > > >
> > > > The user would like to be able to display a list of stored procedures
> > > > in the database. I would like to grant this user the permission to do
> > > > this, but without granting permission to *change* the sp's in any way.
> > > >
> > > > Is there a way to do this?
> > > >
> > > > THANKS
> > > >
> >
> >|||You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'.
You may wish to filter out procedures starting with 'dt'.
Then using dynamic SQL, cycle through the list making the appropriate
changes.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<tootsuite@.gmail.com> wrote in message
news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
>> Yes, in SQL Server 2005 you can grant user "View Definition" permission
>> so
>> that they can see the stored procedures but not alter or execute it.
>> For example:
>> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
>> TO User;
>> GO
>>
>> "tootsuite@.gmail.com" wrote:
>> > Yes, sorry forgot to mention - SQL Server 2005
>> >
>> > I know permissions work differently on 2005 than 2000. Please tell me I
>> > do *not* have to grant execute permission to the user for each and
>> > every user stored procedure in the database'
>> >
>> > Is there another way to allow the user to see?
>> >
>> >
>> >
>> > Arnie Rowland wrote:
>> > > SQL 2000 or SQL 2005?
>> > >
>> > > --
>> > > Arnie Rowland, Ph.D.
>> > > Westwood Consulting, Inc
>> > >
>> > > Most good judgment comes from experience.
>> > > Most experience comes from bad judgment.
>> > > - Anonymous
>> > >
>> > >
>> > > <tootsuite@.gmail.com> wrote in message
>> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
>> > > > Hi,
>> > > >
>> > > > I have a database user with read-only permissions in the database.
>> > > >
>> > > > The user would like to be able to display a list of stored
>> > > > procedures
>> > > > in the database. I would like to grant this user the permission to
>> > > > do
>> > > > this, but without granting permission to *change* the sp's in any
>> > > > way.
>> > > >
>> > > > Is there a way to do this?
>> > > >
>> > > > THANKS
>> > > >
>> >
>> >
>|||There is no single statement, you have to run the grant statement for every
single stored proc. Or you can write a query which would print the grant
statements for you for every stored proc.
"tootsuite@.gmail.com" wrote:
> Hi,
> Thanks - do I have to run this statement for each and every stored
> procedure I wish to grant user permissions on? Or is there a single
> stmt I can use?
> Thanks
>
> Bob wrote:
> > Yes, in SQL Server 2005 you can grant user "View Definition" permission so
> > that they can see the stored procedures but not alter or execute it.
> >
> > For example:
> >
> > GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> > TO User;
> > GO
> >
> >
> >
> > "tootsuite@.gmail.com" wrote:
> >
> > > Yes, sorry forgot to mention - SQL Server 2005
> > >
> > > I know permissions work differently on 2005 than 2000. Please tell me I
> > > do *not* have to grant execute permission to the user for each and
> > > every user stored procedure in the database'
> > >
> > > Is there another way to allow the user to see?
> > >
> > >
> > >
> > > Arnie Rowland wrote:
> > > > SQL 2000 or SQL 2005?
> > > >
> > > > --
> > > > Arnie Rowland, Ph.D.
> > > > Westwood Consulting, Inc
> > > >
> > > > Most good judgment comes from experience.
> > > > Most experience comes from bad judgment.
> > > > - Anonymous
> > > >
> > > >
> > > > <tootsuite@.gmail.com> wrote in message
> > > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> > > > > Hi,
> > > > >
> > > > > I have a database user with read-only permissions in the database.
> > > > >
> > > > > The user would like to be able to display a list of stored procedures
> > > > > in the database. I would like to grant this user the permission to do
> > > > > this, but without granting permission to *change* the sp's in any way.
> > > > >
> > > > > Is there a way to do this?
> > > > >
> > > > > THANKS
> > > > >
> > >
> > >
>|||Yes, that is what I thought - thanks
Arnie Rowland wrote:
> You can create a list of stored procedures from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_TYPE = 'PROCEDURE'.
> You may wish to filter out procedures starting with 'dt'.
> Then using dynamic SQL, cycle through the list making the appropriate
> changes.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <tootsuite@.gmail.com> wrote in message
> news:1157052897.903180.182290@.i3g2000cwc.googlegroups.com...
> > Hi,
> >
> > Thanks - do I have to run this statement for each and every stored
> > procedure I wish to grant user permissions on? Or is there a single
> > stmt I can use?
> >
> > Thanks
> >
> >
> > Bob wrote:
> >> Yes, in SQL Server 2005 you can grant user "View Definition" permission
> >> so
> >> that they can see the stored procedures but not alter or execute it.
> >>
> >> For example:
> >>
> >> GRANT VIEW DEFINITION ON OBJECT::DatabaseNAME.usp_StoredProc
> >> TO User;
> >> GO
> >>
> >>
> >>
> >> "tootsuite@.gmail.com" wrote:
> >>
> >> > Yes, sorry forgot to mention - SQL Server 2005
> >> >
> >> > I know permissions work differently on 2005 than 2000. Please tell me I
> >> > do *not* have to grant execute permission to the user for each and
> >> > every user stored procedure in the database'
> >> >
> >> > Is there another way to allow the user to see?
> >> >
> >> >
> >> >
> >> > Arnie Rowland wrote:
> >> > > SQL 2000 or SQL 2005?
> >> > >
> >> > > --
> >> > > Arnie Rowland, Ph.D.
> >> > > Westwood Consulting, Inc
> >> > >
> >> > > Most good judgment comes from experience.
> >> > > Most experience comes from bad judgment.
> >> > > - Anonymous
> >> > >
> >> > >
> >> > > <tootsuite@.gmail.com> wrote in message
> >> > > news:1156963477.201608.121260@.74g2000cwt.googlegroups.com...
> >> > > > Hi,
> >> > > >
> >> > > > I have a database user with read-only permissions in the database.
> >> > > >
> >> > > > The user would like to be able to display a list of stored
> >> > > > procedures
> >> > > > in the database. I would like to grant this user the permission to
> >> > > > do
> >> > > > this, but without granting permission to *change* the sp's in any
> >> > > > way.
> >> > > >
> >> > > > Is there a way to do this?
> >> > > >
> >> > > > THANKS
> >> > > >
> >> >
> >> >
> >