read only Stored Procedures?

Is there a way to make Stored Procedures "read only" or make it so no one can
modify the SP?
Yes, just don't give users who shouldn't be to change procedures permissions
to do so. If it is an existing problem, remove the relevant users from the
sysadmin, db_owner and db_ddladmin roles. Then you can either REVOKE or DENY
the CREATE PROCEDURE statement to the users.
Jacco Schalkwijk
SQL Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:638520E1-FB4D-4960-8345-56A174249A45@.microsoft.com...
> Is there a way to make Stored Procedures "read only" or make it so no one
> can
> modify the SP?
sql

read only Stored Procedures?

Is there a way to make Stored Procedures "read only" or make it so no one can
modify the SP?Yes, just don't give users who shouldn't be to change procedures permissions
to do so. If it is an existing problem, remove the relevant users from the
sysadmin, db_owner and db_ddladmin roles. Then you can either REVOKE or DENY
the CREATE PROCEDURE statement to the users.
--
Jacco Schalkwijk
SQL Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:638520E1-FB4D-4960-8345-56A174249A45@.microsoft.com...
> Is there a way to make Stored Procedures "read only" or make it so no one
> can
> modify the SP?

Read Only Access to Stored Procedures

We do not allow anyone except DBA's the DBO access permission in our
organization for the "production" environment. The programmers get
DataReader/DataWriter access in production, however, they are wanting
read-only access to the stored procedures.
Does anyone know of a why to give them READ ONLY access to stored procedures
?
Thanks for your help.
ColetteThe only permissions you can grant Users WRT SP's is Execute.
Either a user is allowed to execute a SP or not.
I would seriously re-consider Developer Access to production. Developers
should only be in development, and support staff read only production rights
.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Colette" wrote:

> We do not allow anyone except DBA's the DBO access permission in our
> organization for the "production" environment. The programmers get
> DataReader/DataWriter access in production, however, they are wanting
> read-only access to the stored procedures.
> Does anyone know of a why to give them READ ONLY access to stored procedur
es?
> Thanks for your help.
> Colette

READ COMMITTED SNAPSHOT ON causes performance degradation

I am running a benchmark test with multiple connections running the same
stored procedure with different parameters. This stored procedures does only
SELECT. There are no other activity on the database.
The stored procedure containst this select
SELECT Model,AVG(Price),MIN(Price),MAX(Price),COUNT(*)
FROM SH_Product
WHERE Project_Number = @.Station
AND EmployeeID = 0
AND Type = @.Match100
GROUP BY Model
ORDER BY Model
When the database is set in READ COMMITTED SNAPSHOT OFF mode, the number of
transactions per second increases linearly as more and more connections are
added.
But when the database is set to READ COMMITTED SNAPSHOT ON, the performance
degrades after 20 users, the total transactions processed per second remains
constant when number of users increase. That means for each user the
transactions per second reduces.
I can understand this if there was any other INSERT/UPDATE/DELETE activity
happening on the database, as SELECT will have to traverse the row version
chain to get the data, but in SELECT only environment, how can the
performance degrade.
With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
overhead for SQL Server. I have a PSS ticket open for this, but I am getting
a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
row version it is slower, but my point is if there is no data change why does
SQL Server has to go to tempdb?
Am I missing something?. Please help.
Thank youOn Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
(snip)
>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>happening on the database, as SELECT will have to traverse the row version
>chain to get the data, but in SELECT only environment, how can the
>performance degrade.
>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>overhead for SQL Server. I have a PSS ticket open for this, but I am getting
>a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
>row version it is slower, but my point is if there is no data change why does
>SQL Server has to go to tempdb?
Hi Shailesh,
I'm not intimately familiar with the internals of READ COMMITTED
SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
can't know that there are no previous row versions there without looking
first.
Have you considered setting the database to READ ONLY? That will fully
eliminate all locking overhead.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Thanks Hugo
I am seeing this behavior while running a benchmark, which has different
sets of tests, one of them being CPU intensive test which only does SELECT.
It is not on a real production database so putting database in READ ONLY
mode is not an issue, but I wanted to understand the performance issue
without doing it.
I looked at page file structure in Kalen Delaney's book and I don't see any
information about whether SQL server puts a status bit on the page itself
for locked rows. But with READ COMMITTED SNAPSHOT ON, SQL server puts a 14
byte data in each row to store Transaction Sequence number (XSN), it is only
added when the row is updated. So logically speaking when a connection tries
to SELECT from a row, it has a XSN and when it goes to check the row in disk
if there is no XSN field then it should immediately know that the row is not
modified and should not go to tempdb to check.
Even if there is XSN for the row, and if it's value is less than SELECT XSN
then it should check lock records before going to tempdb. And this overhead
is also incurred when database is in READ COMMITTED SNAPSHOT OFF mode. So I
don't really get why the performance suffers so much.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:m3oqf31go4ti3d37736nsegqp2patoqjin@.4ax.com...
> On Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
> (snip)
>>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>>happening on the database, as SELECT will have to traverse the row version
>>chain to get the data, but in SELECT only environment, how can the
>>performance degrade.
>>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>>overhead for SQL Server. I have a PSS ticket open for this, but I am
>>getting
>>a satisfactory answer. All I get is since SELECT needs to go to tempdb to
>>get
>>row version it is slower, but my point is if there is no data change why
>>does
>>SQL Server has to go to tempdb?
> Hi Shailesh,
> I'm not intimately familiar with the internals of READ COMMITTED
> SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
> can't know that there are no previous row versions there without looking
> first.
> Have you considered setting the database to READ ONLY? That will fully
> eliminate all locking overhead.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Re: Set permissions on stored procedures

Hello to all,
How do I set permission to allow some users to Execute the stored procedures without setting indiviually in ea. stored procedure?
Thank you much!You don't

Place the users in a role and grant permission to the role.

Re-"pointing" Stored Procedures

How do I "point" a set of stored procedures to operate on different linked servers?

In other words, I have the following linked servers:

DatabaseA
DatabaseB
DatabaseC
DatabaseD
...and in the future, there may be added additional linked servers.

All the linked servers have identical schema, but they contain unique data--each linked server represents a company.

I have a database which will contain stored procedures which I will want to operate against these linked servers. How can I "redirect" my stored procedures to operate against a chosen linked server?

If these were not linked servers, but SQL Server databases, I'd be able to replicate the same stored procedures in each database. Then, when I called a stored procedure, it would act against the data in that database. But these aren't SQL Server databases, so that idea is out.

Unfortunately, the USE command cannot be used within a stored procedure, and even if you could, I can't get it to respond to a database name given as a variable. That idea is out.

The only alternative I have left is to use the string catenation facility of the EXECUTE command. Unfortunately, with 100s of complex queries, setting that up is going to be a nightmare.

Does anyone have any ideas?With all due respect, having separate companies in separate databases is a bad idea. You've set a new standard - separate companies in separate databases on linked servers that are not SQL Server. Congrats.

Assuming this design is something over which you have no control (sounds like a consulting gig), how 'bout another new standard - separate SQL Server databases with stored procedures that act on separate linked servers (which are really linked databases) that are not SQL Server? You maintain 1:1 relationship b/w SQL Server databases and the linked servers their stored procedures act on.

Then, you write stored procedure generator that replaces all linked server references with the 4-part name to which the db corresponds (using syscomments, PatIndex and cursors) and sticks 'em in the right database. Ugly, eh? Remember, we're reaching for new heights.

You will need a separate database that is your (source) control database in which your stored procedures originate - sounds like you already have that - and a table that maps your SQL Server databases to your linked servers.

Good luck.|||Wow, that sounds like one heck of a way to get hurt really, really bad!

Could you build views that combine the data from all of the various servers, or DTS packages to scoop them into a single container to make management simpler?

If you really, truly want to continue to process the data on N different servers (with N being a variable, not a constant), then I'd suggest that you convert your stored procedures to DTS packages... These could operate against DSN or udl names, which would at least confine the chaos to a much smaller area.

Good luck!
-PatP|||Pat and Max, you guys both came up with good ideas.

Yes, this is a consulting gig. Yes, these are pre-existing, PervasiveSQL databases which are the backend to someone else's product. The separate database for each company is thier concept and there is nothing I can do about that.

We have an existing reporting package that operates against an MSSQL backend. Our client has asked us to port our product to work with this PervasiveSQL-multi-company arrangement.

I like both your ideas. Here's another I came up with while waiting for responses:

Write my stored procedures using a token for the database name. Store those stored procedures as text in a table, much like syscomments stores stored procedures. Then write a "master" execution stored procedure which loads the stored procedure text from that table, uses the replace command to substitute the correct database name for the token, and then use the EXECUTE command to execute the stored procedure. The problem I see with my idea, as compared to your ideas, is that I am not acutally executing stored procedures, hence I lose any precompilation advantage of a true stored procedure.

Thanks for you ideas guys.|||The problem with objects being directly accessed from a linked server is the long naming convention. I guess you guys would agree that most bugs related to objects from linked servers were due to typos on the full object name.

In one of my projects here in the Philippines, a GIS application needs to pull data from a central data repository. The problem is the GIS server (with its own database server) is not allowed to directly access the data repository.

As a work around, I defined both the GIS database server and the central repository in another server as linked servers. Thus, the two servers are linked via the third server. then I did the following:

1. I defined details of the data to needed by the GIS apps and put it in a view in the central repository.
CREATE VIEW vw_PROPERTY_RE_GIS
AS
SELECT B.s_prop_no, B.s_re_no, A.s_PLOP, A.s_loc_id
FROM T_PROPERTY A, T_RE_PROPERTY B
WHERE A.s_prop_no = B.s_prop_no AND A.s_loc_id = b.s_loc_id

2. Defined a view in the third to access the view in the data repository.
CREATE VIEW vw_PROPERTY_RE_GIS
as
SELECT * FROM CNTRL_DB.REMS.REMS.vw_PROPERTY_RE_GIS
3. Defined a view in the GIS DB to access the view in the third server.
CREATE VIEW vw_PROPERTY_RE_GIS
as
SELECT * FROM ALPS.REMS.ALPS.vw_PROPERTY_RE_GIS

Thus, whenever my GIS app would need information about a certain real estate, it would simply kick the simple query SELECT * FROM vw_PROPERTY_RE_GIS.

And if by chance there is a need to add more fields on the data to be extracted, I would just have to modifiy the view on the central repository.

This approach works if there is restriction on how servers are linked and performance is not so much an issue.|||I would NEVER recommend this, but you can use the USE statement in a stored procedure and pass the linked server name as a variable. But you have to use...wait for it...DYNAMIC SQL.

(Pause for shrieks of horror from all competent DBAs...)

I've only implemented this once, for a database schema snapshot application that had to run against any and all databases on a server. It was a mess to program, but it has run very smoothly and reliably since then. The code hasn't changed much in four years and two SQL Server upgrades.|||Blindman,

How were you able to accomplish this ?

I've attempted to use the USE statement in stored procedures and, when compiled, they've always come back with an error stating the the USE statement is not permited in stored procedures. The USE page in the Books Online also state this.

Additionally, the use of a variable for the database with the USE statment is also prohibited. I've tested this several times and have not been successful. Apparently, the USE statement requires a literal for the database name.

If you've found some way to get around these problem, let me know, 'cause they'd make tackling these problems a whole lot easier.

Thanks.

Ken|||create procedure GetDataFromPubs
as
begin
declare @.SQLString varchar(4000)
set @.SQLString = 'Use Pubs Select * from Authors'
execute (@.SQLString)
end

The executed statement runs in it's own scope, and so after completion focus returns to the calling database.