How to revoke write, update access from public role

Hi,
I have an application that gives all the write, update access to public role
, how could I quickly revoke all the Write and Update access from Public, in
stead of uncheck all the checked boxes?
Thanks.Hi
Execute the following in Query Analyzer with text result (Query menu click
result in text) and copy and paste results
to give you the required script.
select 'revoke all on ' + QUOTENAME(name) + ' from [public]'
from sysobjects where type in('U','P','V','TR','IF','FN')
and objectproperty(id,'IsMSShipped')=0
go
Tahnks
Hari
MCDBA
"Hong" <anonymous@.discussions.microsoft.com> wrote in message
news:41AE885E-A0CB-4542-B6A9-843454B33A66@.microsoft.com...
> Hi,
> I have an application that gives all the write, update access to public
role, how could I quickly revoke all the Write and Update access from
Public, instead of uncheck all the checked boxes?
> Thanks.

How to Return a subset of resultset of some stored proc

Hi,

I want to write a query to select some of the columns from result of stored proc,

My expected code will be like that, but its not allowed,

Select first_Name, Last_Name, Last_Used_date from (Execute sp_reportNumber15 '9555')

I have around 30 different stored proc but I know some of the columns are in resultset of every stored proc, So I want to write a generalize stored proc to whom I will just pass stored proc name and it will return me the subset of its result.

Thanks,

Imran.

Imran:

I would first try to convince you to take another approach. Reports -- especially heavy reports -- can be very resource intensive. And to take the results of some 30 different reports and the massage these results and take small subsets of each report and the presenting the results to an end user sounds like doing a lot of work to simplify the coding of a developer. To me this is putting the work in the wrong place.

When I implement a database one of my goals always is to provide service to each request as fast as possible. The implementation you propose does not aim at that goal. To me, the implementation you propose aims at taking a round-about path to gathering the data and hoping that the response to the request might be fast enough. This brings to me visions of dozens of tables being table scanned when the data from much fewer tables is needed. If it were my server I would view this as abuse.

Please, rethink this before you go forward.

Dave

|||

Hi Dave,

I have not written that I want to compile resultset of 30 reports at the same time. The scenario is like as,

One enduser want sometimes 5 specific columns from resultset of sp_report1, sometime he needs same 5 columns from resultset of sp_report2, in actual the number of columns returned in resultsets is different of sp_report1 and sp_report2 but same 5 columns are present in resultset of every sp, like sp_report1 return 30 columns, and sp_report4 returns 15 columns.

I want to write a new stored proc for him, say for example sp_getData 'sp_report1'. By this kind of stored proc he doesnt need to fetch full resultset of every sp, this will reduce network load also,

when user passes the parameter value as sp_report1, then I will return 5 columns from resultset of report1, and when user passes parameter value as 'sp_report15' then I will return 5 columns from resultset of report15

My question is only that, if you have a vision to write this kind of statement, conceptually my statement will be as,

Select Col1, Col4, Col6, Col7, Col9 from (Exec sp_getData @.repnumber)

but syntactically it is wrong, if there is some possibility then just reply otherwise dont waste your time as well as my time.

thanks,

Imran.

|||

convert your sp into function..

so you can do Select Col1, Col4, Col6, Col7, Col9 from fn_getData (@.repnumber) as Data

|||

Thank you so much ... :D

This is exactly what I want...

so nice of you

how to retrieve full text of procedure definition from syscomment

Hello,
I am trying to locate where/when data is being inserted into a table from a
DB I recently inherited. So I write this in QA
select * from syscomments where
text like '%Insert Into CompareSubscribers%'
This will retrieve the procedure(s) that contains the text "...Insert
Into..." But when I select that text field from QA Results, and paste it
into Notepad, I only get the characters before the first carriage retuen.
How can I retrieve the full text? Or if I stretch out the field in QA
Results grid - it only stretches to the first carriage return in the text.
Thanks,
RichHi Rich
Why don't you try returning the results in text format instead of grid? And
only select the text column:
select text from syscomments where
text like '%Insert Into CompareSubscribers%'
Make sure you configure QA to return the full column width.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:164BF9D0-FEDE-4B37-8CAE-9A8F7257F118@.microsoft.com...
> Hello,
> I am trying to locate where/when data is being inserted into a table from
> a
> DB I recently inherited. So I write this in QA
> select * from syscomments where
> text like '%Insert Into CompareSubscribers%'
> This will retrieve the procedure(s) that contains the text "...Insert
> Into..." But when I select that text field from QA Results, and paste it
> into Notepad, I only get the characters before the first carriage retuen.
> How can I retrieve the full text? Or if I stretch out the field in QA
> Results grid - it only stretches to the first carriage return in the text.
> Thanks,
> Rich