Petterns for SQL Tables and Stored Procedures

I am looking for some patterns in SQL Server. The Patterns and Paractices
did not seem to have what I am looking for, but it would seem to me that it
is a very commmon scenario that must have been covered.
Basically, it is the problem of aggregating data for overviews.
Assume the following:
Table Services
[SID][ServiceName][ServiceCategoryID][PI
D] [AmmountDue]
Table Payer
[PID][PayerCategory][PayerName]
Table Payments
[PayID][SID][Date][Amount][PID] ** Occasionally a third party might pay
for someone else
Table ServiceCategory
[ServiceCategoryID][ServiceCategoryName]
Table PayerCategory
[PayerCategoryID][PayerCategoryName]
Now, I want to get a summary of the data very quickly that breaks things
down like:
By ServiceCategory
TotalAmountPaid TotalDue AmountDueFrom30DaysAgo ADF31-60DaysAgo
adf61-90Days Ago
Then break these down by PayerCategory
This would seem like a common type of thing, and Ican think of ways to do
this but that take a lot of time, if there are millions of rows, and I can
imagine that triggers might be useful here to keep up to date, but I am
unfamiliar with them.
If you can give me any guidance on this it owuld be helpful. For extra
points, what about being able to dynamically change the periods from say
0-30days to 0-15 days)
Thanks a lot
BBFor starters, you need to post some ddl, sample data and expected results.
Not just a narrative.
I can tell you this though - without dates in your services table or
payments table to know when the service and payments took place, what you're
looking for is impossible.
"bobbyballgame" wrote:

> I am looking for some patterns in SQL Server. The Patterns and Paractices
> did not seem to have what I am looking for, but it would seem to me that i
t
> is a very commmon scenario that must have been covered.
> Basically, it is the problem of aggregating data for overviews.
> Assume the following:
> Table Services
> [SID][ServiceName][ServiceCategoryID][PI
D] [AmmountDue]
> Table Payer
> [PID][PayerCategory][PayerName]
> Table Payments
> [PayID][SID][Date][Amount][PID] ** Occasionally a third party might pay
> for someone else
> Table ServiceCategory
> [ServiceCategoryID][ServiceCategoryName]
> Table PayerCategory
> [PayerCategoryID][PayerCategoryName]
>
> Now, I want to get a summary of the data very quickly that breaks things
> down like:
> By ServiceCategory
> TotalAmountPaid TotalDue AmountDueFrom30DaysAgo ADF31-60DaysAgo
> adf61-90Days Ago
> Then break these down by PayerCategory
>
> This would seem like a common type of thing, and Ican think of ways to do
> this but that take a lot of time, if there are millions of rows, and I can
> imagine that triggers might be useful here to keep up to date, but I am
> unfamiliar with them.
> If you can give me any guidance on this it owuld be helpful. For extra
> points, what about being able to dynamically change the periods from say
> 0-30days to 0-15 days)
> Thanks a lot
> BB
>
>
>|||Steve,
Thanks. The tables are internal ( I would not be allowed to post them) and a
lot more complicated. For example the Payments Table has 31 fields in it, so
I was trying to simplify.
The Service does have a Date field. Sorry about the ommission. Really, I am
looking for a general pattern for the problem of needing aggregate data from
many, amny rows quickly, so I thought a narrative would be more useful.
I will work on a model that is a little more simple, and for what is worth,
I need the data in XML format from SQL 2000.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:070D02CF-7367-4593-91F4-6533588D830E@.microsoft.com...
> For starters, you need to post some ddl, sample data and expected results.
> Not just a narrative.
> I can tell you this though - without dates in your services table or
> payments table to know when the service and payments took place, what
> you're
> looking for is impossible.
>
> "bobbyballgame" wrote:
>

Persisting XML

Hi all,
I would like to save the output of a FOR XML stored procedure to a file on my hard drive with a filename of my choosing. Can anybody point me in the write direction on how to do this. (I've got the FOR XML sproc written, I just need help figuring out ho
w to persist it to a file. Thanks.
JT
Hi JT,
I noticed you make another post with topic 'Persist Sproc OUTPUT' in the
newsgroup: microsoft.public.sqlserver.programming. I found an community
member has added his reply to that thread and I will add my reply if you
have follow up questions to that thread
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||See the thread entitled "extracting results of XML stored proc to file"
started by Mike UK in this newsgroup.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"JT" <JTnospam@.verizon.net> wrote in message
news:A3758B34-4AC8-4675-8326-319C40508CFF@.microsoft.com...
> Hi all,
> I would like to save the output of a FOR XML stored procedure to a file on
my hard drive with a filename of my choosing. Can anybody point me in the
write direction on how to do this. (I've got the FOR XML sproc written, I
just need help figuring out how to persist it to a file. Thanks.
> JT
|||Thanks Graeme.
JT

Persistence Of Temporary Tables

Is it possible to create a temporary table in a 'parent' stored procedure and then access it from a 'child' or nested stored procedure? Bearing in mind that the child proc will definitely be called by the parent proc.

Look at the article from Erland:

http://www.sommarskog.se/share_data.html

HTH, jens Suessmeyer.

sql

Per-record processing?

Hi,

I am looking for a way to process each record from a SELECT query. Each record has to go through a stored procedure to be validated and different actions have to be taken depending on whether or not it passes the validation.

I'm a bit new to Transact SQL. My background is in programming, so I keep wanting to visualize a loop, which doesn't exist in TSQL, from what I understand. Can anyone help me understand how something of this type would be done?

Thank you!

SQL Server is designed for SET based operation. 'Per-Record' operations are often best left to a client application.

However, in some situations, it may be necessary to (a) gather relavent records into a temporary table, (b) set a counter variable with the @.@.Rowcount from the temporary table, (c) use a WHILE @.CurrentRecord < @.RecordCount type of loop.

However, this is guarenteed to be a non-optimal use of SQL Server. Perhaps if you were to post the select query, the stored procedure, and some sample data, folks here would help you properly design a SET based operation -if possible.

|||Thank you. I think this is just what I needed to move this into a .Net app.sql

Permissions...

I have been looking for an example of applying permissions to all the
tables, view, stored procs, etc., in X sql database without much luck.
Clearly, it's simple to do for a single record but I have over 500 tables,
views, etc., that need to have permissions assigned for SELECT, INSERT,
UPDATE and DELETE for the tables (EXECUTE for the stored procs).
The must be an example that I am missing that allows me to assign all these
permissions quickly, we have a tool within our application but since I have
no permission currently set I cannot even get to that tool to run it.
Thanks again - I always get assistance from users here! It's the best part
about moving our app from VFP data to SQL data.
Chrisyou can put users in db_datareader and db_datawriter roles to get select,
insert, delete and update permissions.
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>I have been looking for an example of applying permissions to all the
>tables, view, stored procs, etc., in X sql database without much luck.
>Clearly, it's simple to do for a single record but I have over 500 tables,
>views, etc., that need to have permissions assigned for SELECT, INSERT,
>UPDATE and DELETE for the tables (EXECUTE for the stored procs).
> The must be an example that I am missing that allows me to assign all
> these permissions quickly, we have a tool within our application but since
> I have no permission currently set I cannot even get to that tool to run
> it.
> Thanks again - I always get assistance from users here! It's the best
> part about moving our app from VFP data to SQL data.
> Chris
>|||Hi,
Use the database level fixed roles db_datareader and db_datawriter to
assign SELECT, UPDATE, INSERT, DELETE previlages for all
TABLES/VIEW. But for stored procedure Execution rights there is no roles
available. Only way is to give Execute previlages individually.
GRANT EXEC on Proc_name to Username
If you have numerous procs then write a small script to query the sysobjects
table for Xtype ='P' to get all the procedure names and use the above Grant
statement inside a cursor.
Thanks
Hari
Sql Server Mvp
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>I have been looking for an example of applying permissions to all the
>tables, view, stored procs, etc., in X sql database without much luck.
>Clearly, it's simple to do for a single record but I have over 500 tables,
>views, etc., that need to have permissions assigned for SELECT, INSERT,
>UPDATE and DELETE for the tables (EXECUTE for the stored procs).
> The must be an example that I am missing that allows me to assign all
> these permissions quickly, we have a tool within our application but since
> I have no permission currently set I cannot even get to that tool to run
> it.
> Thanks again - I always get assistance from users here! It's the best
> part about moving our app from VFP data to SQL data.
> Chris
>|||Thank you!!!!!
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:exHE4XWXFHA.628@.tk2msftngp13.phx.gbl...
> Hi,
> Use the database level fixed roles db_datareader and db_datawriter to
> assign SELECT, UPDATE, INSERT, DELETE previlages for all
> TABLES/VIEW. But for stored procedure Execution rights there is no roles
> available. Only way is to give Execute previlages individually.
> GRANT EXEC on Proc_name to Username
> If you have numerous procs then write a small script to query the
> sysobjects table for Xtype ='P' to get all the procedure names and use the
> above Grant
> statement inside a cursor.
> Thanks
> Hari
> Sql Server Mvp
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>|||Thank you!!!!
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:eHa6nNWXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> you can put users in db_datareader and db_datawriter roles to get select,
> insert, delete and update permissions.
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>sql

Permissions With Dynamic SQL Within Stored Procedure

Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. Basically, here's the scenario...

I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.

The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.

Basically, the connection context is using security for a low-privilaged Windows account ("UserX") and UserX has no permission to the table referenced in the dynamic SQL but because of the dyanmic nature of the query, the stored procedure ends up adopting the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.

Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.

So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?

I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may be as secure, but simpler.

PS - Please don't let this degenerate into a conversation about OR mappers. I know that happens a lot on these forums.

BoulderBum:

Basically, the connection context is using security for a low-privilaged Windows account ("UserX") and UserX has no permission to the table referenced in the dynamic SQL but because of the dyanmic nature of the query, the stored procedure ends up adopting the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.

Yes, this is by design. When you EXECUTE dynamic SQL statement. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure.

So in earlier SQL versions you have to choose: use dynamic SQL if you do have to, or not use it if not necessary. Fortunately in SQL 2005 we have another option: using EXECUTE AS clause when creating the stored procedure. For example:

create proc sp_testper WITH EXECUTE AS SELF
as
EXEC('select * from Orders')
go

For more information about this clause, please refer to:

http://msdn2.microsoft.com/en-us/library/ms188354(d=ide).aspx

|||

Thanks, lori_Jay.

That's the solution I'm probably going to end up going with, though I didn't know until a few days ago that EXECUTE AS could apply toCLRstored procedures. I was just deploying via the IDE which doesn't offer such options and the documentation I saw didn't give an example of how to use EXECUTE AS with a CLR stored procedure.

Luckily I was informed that it was indeed possible and though I haven't sat down to do it yet, and that solution suffices for my needs!

Anyway, thanks again!

|||

For future readers, something else I discovered comes in handy is that you can have a predeployscripts.sql and postdeployscripts.sql file in a database project.

With those files, I was able to rig the EXECUTE AS and change the schema for my stored procedures. It took some dropping/recreating to get everything where I wanted, but it was pretty easy and worked like a charm.

I now have one-touch deployment of my CLR stored procedures through Visual Studio again!

permissions to view jobs but not change them

is there any combination of permissions or a role that will let a user view
stored procedures and their success without being able to modify them? Thanks.What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
unless you have encrypted the procedure (then no-one can do that).
Also, what do you mean by "and their success"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> is there any combination of permissions or a role that will let a user view
> stored procedures and their success without being able to modify them? Thanks.|||Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
there a combination of permissions or a role that would let a user view a job
and check its success without being able to modify it in any way. Thanks.
"Tibor Karaszi" wrote:
> What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do that
> unless you have encrypted the procedure (then no-one can do that).
> Also, what do you mean by "and their success"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> > is there any combination of permissions or a role that will let a user view
> > stored procedures and their success without being able to modify them? Thanks.
>
>|||There is no such feature. SQL Server Agent doesn't have any "role" scheme or similar. You could try
playing with the TargetServersRole. It *might* do what you want, but it is designed for something
else...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:F0678877-B513-4AC6-BE4F-DA44830D9048@.microsoft.com...
> Sorry, I meant view jobs... in the Jobs viewer in Enterprise Manager. Is
> there a combination of permissions or a role that would let a user view a job
> and check its success without being able to modify it in any way. Thanks.
> "Tibor Karaszi" wrote:
> > What do you exactly mean by "view stored procedures"? Look at the source code? Everyone can do
that
> > unless you have encrypted the procedure (then no-one can do that).
> >
> > Also, what do you mean by "and their success"?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
> > news:7D66188B-9C98-42B3-8989-8CC23E7C97DC@.microsoft.com...
> > > is there any combination of permissions or a role that will let a user view
> > > stored procedures and their success without being able to modify them? Thanks.
> >
> >
> >

Permissions to view code but not modify (Help)

Hello Everyone,
I am new to SQL 2005 and am trying to setup permissions for the vb .net
programmers.
I want them to be able to look at the stored procedure/view/functions code
but not be able to modify the code.
How can I accomplish this.
Thanks for all the help
Shabnam
Grant them VIEW DEFINTION permissions. Either at the server, database, schema or object level.
See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/151b7a2e-ab97-42ba-baf0-6929c5334e29.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shabnam Gupta" <sgupta@.evcsolutions.com> wrote in message
news:ePR8IIhFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hello Everyone,
> I am new to SQL 2005 and am trying to setup permissions for the vb .net programmers.
> I want them to be able to look at the stored procedure/view/functions code but not be able to
> modify the code.
> How can I accomplish this.
>
> Thanks for all the help
> Shabnam
>

Permissions to view code but not modify (Help)

Hello Everyone,
I am new to SQL 2005 and am trying to setup permissions for the vb .net
programmers.
I want them to be able to look at the stored procedure/view/functions code
but not be able to modify the code.
How can I accomplish this.
Thanks for all the help
ShabnamGrant them VIEW DEFINTION permissions. Either at the server, database, schema or object level.
See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/151b7a2e-ab97-42ba-baf0-6929c5334e29.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shabnam Gupta" <sgupta@.evcsolutions.com> wrote in message
news:ePR8IIhFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hello Everyone,
> I am new to SQL 2005 and am trying to setup permissions for the vb .net programmers.
> I want them to be able to look at the stored procedure/view/functions code but not be able to
> modify the code.
> How can I accomplish this.
>
> Thanks for all the help
> Shabnam
>sql

Permissions to view code but not modify (Help)

Hello Everyone,
I am new to SQL 2005 and am trying to setup permissions for the vb .net
programmers.
I want them to be able to look at the stored procedure/view/functions code
but not be able to modify the code.
How can I accomplish this.
Thanks for all the help
ShabnamGrant them VIEW DEFINTION permissions. Either at the server, database, schem
a or object level.
See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/151b7a2e-ab97-42ba-baf0-
6929c5334e29.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Shabnam Gupta" <sgupta@.evcsolutions.com> wrote in message
news:ePR8IIhFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Hello Everyone,
> I am new to SQL 2005 and am trying to setup permissions for the vb .net pr
ogrammers.
> I want them to be able to look at the stored procedure/view/functions code
but not be able to
> modify the code.
> How can I accomplish this.
>
> Thanks for all the help
> Shabnam
>

Permissions to allow updating stored procedures

Has anyone worked out what permissions are required such
that a given database user can create, update and/or
delete stored procedures, but not do the same to
tables/views ?CREATE PROCEDURE permission is needed to modify SPs. CREATE VIEW is a
separate permission.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Jim Trowbridge" <jtrowbridge@.adelaidebank.com.au> wrote in message
news:fd1001c40d60$e1f1acb0$a301280a@.phx.gbl...
> Has anyone worked out what permissions are required such
> that a given database user can create, update and/or
> delete stored procedures, but not do the same to
> tables/views ?

permissions required for executing CDOSys stored procedures

Hi there
I'm doing some analysis on the database applications in my organisation
before migrating the databases to a new server. One of these makes use of
CDOSys objects for sending mail, instead of SQL Mail. There are a number of
stored procedures within the database that call the sp_OACreate &
sp_OASetProperty. Apparently only members of the sysadmin role can execute
these stored procedures, however, the sql login for this application is not
a
sysadmin! I thought perhaps there was a mistake in books online but i've
looked on google and the permission requirements are the same - must be
sysadmin. Any ideas how it still manages to function without these rights?
Thanks in advance!Neile,
Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
SomeOtherPrincipal.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Thanks for the response. I had a look at the permissions on the stored
procedures and could see none granted on the login in question... bizarre.
I
assume the way to check this is by going into the procedure properties scree
n
and clicking on the permissions tab? Is there another view somewhere else'
thx
"Russell Fields" wrote:

> Neile,
> Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
> SomeOtherPrincipal.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>
>|||Neile,
I usually use sp_helprotect to investigate these things. E.g.
sp_helprotect @.name='sp_OACreate'
I don't know what version of SQL Server you are using, but in 2005 the
properties of the sp_OACreate extended stored procedure has a permissions
page that shows this also, at least in SP2. With SQL Server 2000 Enterprise
Manager there is a permissions button on the properties form that serves a
similar purpose.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I had a look at the permissions on the stored
> procedures and could see none granted on the login in question...
> bizarre. I
> assume the way to check this is by going into the procedure properties
> screen
> and clicking on the permissions tab? Is there another view somewhere
> else'
> thx
> "Russell Fields" wrote:
>|||Hi Russell,
Thanks for this. It's a sql 2000 box and i had checked the permission
properties page as you suggested below but it was blank. So it appears that
the these stored procedures are being executed by a user who is not a
sysadmin and who has not been granted specific execute permissions on the
procedures.
Is this possible'
Thanks
"Russell Fields" wrote:

> Neile,
> I usually use sp_helprotect to investigate these things. E.g.
> sp_helprotect @.name='sp_OACreate'
> I don't know what version of SQL Server you are using, but in 2005 the
> properties of the sp_OACreate extended stored procedure has a permissions
> page that shows this also, at least in SP2. With SQL Server 2000 Enterpri
se
> Manager there is a permissions button on the properties form that serves a
> similar purpose.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...
>
>|||Neile,
I don't know what to say about a blank properties page except, "I don't
know." Of course, the sp_OA procedures can be granted to public, which
would give everybody rights, but would also result in public having a green
check box next to it.
What was the response to: sp_helprotect @.name='sp_OACreate'
Another possibility is that the user is also in the local administrators
group of the server. If so, he is by default member of the sysadmin server
role on the SQL Server database unless steps are taken to prevent that. For
example, as described in http://support.microsoft.com/kb/263712/.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> Thanks for this. It's a sql 2000 box and i had checked the permission
> properties page as you suggested below but it was blank. So it appears
> that
> the these stored procedures are being executed by a user who is not a
> sysadmin and who has not been granted specific execute permissions on the
> procedures.
> Is this possible'
> Thanks
> "Russell Fields" wrote:
>|||Oh, and if the user is db_owner in master he also can execute sp_OA
procedures.
The BOL does not say this is possible, but I just tested it and it does work
on SQL 2000 and 2005. (So is this an 'undocumented' feature or an error in
documentation? I have reported it as an error.)
RLF
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OdHHkzqrHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Neile,
> I don't know what to say about a blank properties page except, "I don't
> know." Of course, the sp_OA procedures can be granted to public, which
> would give everybody rights, but would also result in public having a
> green check box next to it.
> What was the response to: sp_helprotect @.name='sp_OACreate'
> Another possibility is that the user is also in the local administrators
> group of the server. If so, he is by default member of the sysadmin
> server role on the SQL Server database unless steps are taken to prevent
> that. For example, as described in
> http://support.microsoft.com/kb/263712/.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...
>|||Perhaps the cross-database chaining (a.k.a. db_chaining) database option is
turned on. In that case, execute permissions on indirectly referenced
objects in other databases are not needed as long as the ownership chain is
unbroken. Users need execute permissions on only the user stored procedure.
Note that the user database must be owned by 'sa' to maintain an unbroken
chain to master database objects. It is important that only sysadmin users
be allowed to create dbo-owned objects in this scenario in order to prevent
elevation of privileges.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Dan,
Thanks for that reminder. We broke our ownership chains many years ago,
well before SQL 2000 SP3, by giving each database a different owner. It had
faded from memory.
RLF
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
> Perhaps the cross-database chaining (a.k.a. db_chaining) database option
> is turned on. In that case, execute permissions on indirectly referenced
> objects in other databases are not needed as long as the ownership chain
> is unbroken. Users need execute permissions on only the user stored
> procedure.
> Note that the user database must be owned by 'sa' to maintain an unbroken
> chain to master database objects. It is important that only sysadmin
> users be allowed to create dbo-owned objects in this scenario in order to
> prevent elevation of privileges.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>|||Hi Russell
Thanks for all the tips - this is all useful stuff to know. I discovered
that the stored procedure in the database which in turn calls the CDOSys
stored procedures is executed via a SQL job - and thus in the context of the
job owner, which is sa. So that explains it!!
But thanks for your suggestions and thanks to Dan for the info about cross
db chaining - all good to know.
Cheers
Neile
"Russell Fields" wrote:

> Dan,
> Thanks for that reminder. We broke our ownership chains many years ago,
> well before SQL 2000 SP3, by giving each database a different owner. It h
ad
> faded from memory.
> RLF
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
>
>

Permissions Question For SQL Login Account

I have a SQL login account defined with DBO permissions on a particular
database. When this login attempts to run the sp_updatestats stored
procedure, the following error occurs: "User does not have permission to
perform this action." According to BOL, the DBO has permissions to execute
this stored procedure. Any help would be appreciated.MACason wrote:
> I have a SQL login account defined with DBO permissions on a
> particular database. When this login attempts to run the
> sp_updatestats stored procedure, the following error occurs: "User
> does not have permission to perform this action." According to BOL,
> the DBO has permissions to execute this stored procedure. Any help
> would be appreciated.
Run this code (from sp_updatestats) and see what it returns. If it fails
then the user is not a sysadmin and not the dbo in the database. There
is only one dbo per database (they cannot be aliased as dbo I don't
think):
DECLARE @.dbsid varbinary(85)
SELECT @.dbsid = sid
FROM master.dbo.sysdatabases
WHERE name = db_name()
select @.dbsid
select suser_sid()
/*Check the user sysadmin*/
IF NOT is_srvrolemember('sysadmin') = 1 AND suser_sid() <> @.dbsid
BEGIN
RAISERROR(15247,-1,-1)
RETURN (1)
END
David Gugick
Imceda Software
www.imceda.com|||The Books Online states:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_ua-uz_14kz.htm">
Only the DBO and members of the sysadmin fixed server role can execute this
procedure.
</Excerpt>
Note that db_owner role members are not *the* 'dbo'. The database owner is
the login that owns the database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:47413145-85BC-4F7C-AC60-245E011F324D@.microsoft.com...
>I have a SQL login account defined with DBO permissions on a particular
> database. When this login attempts to run the sp_updatestats stored
> procedure, the following error occurs: "User does not have permission
> to
> perform this action." According to BOL, the DBO has permissions to execute
> this stored procedure. Any help would be appreciated.

Permissions Problem using Dynamic SQL

Hi all!

I've got a problem where I have created a stored procedure (using MS SQL Server 2000) that does a temporary table creation:
CREATE #tmpData ( [some_fields] )
and then it uses dynamic SQL to populate the data
SELECT @.ExecStr =
'INSERT INTO #tmpData
SELECT * FROM tData
WHERE [some_condition]
ORDER BY ' + @.SortColumn /* input parm to the stored proc */
EXEC (@.ExecStr)

I get a permissions error 229 when I try to run this because my user only has execute permissions for the stored procedure within the database. The only thing that I've found so far that will fix this is if I change the user's permissions to db_owner, which I don't want to do.

I've tried to explicitly grant permission within the stored proc, but since the object (the temp table) does not actually reside in the database, that gives me an error as well (4610: You can only grant or revoke permissions on objects in the current database.).

Is there anything else I can do? I really don't want to have to give the user that much freedom within the database, and removing the dynamic SQL really isn't a viable option either.

Thanks in advance for your help!
CatDynamic SQL executes within its own scope, not that of the stored procedure. So it does not inherit the stored procedures rights, and only operates under the connection's rights.
You'll need to grant READ permission to the user on table tData.|||Try this ... put your insert before your exec ...


SET NOCOUNT ON
USE master

create table #temp (dbname sysname)

declare @.sql nvarchar(255)
select @.sql = 'select name from sysdatabases'

insert into #temp
exec sp_executesql @.sql

select * from #temp

drop table #temp|||Thanks, Tom!

That was a good idea. Unfortunately it still gives me the same error.

Thanks again! If you have any other ideas, let me know.
Cat|||Your user is going to have to have select permissions on tData.|||Excellent suggestion.
You'll need to grant READ permission to the user on table tData.|||Thanks everyone!

I did get this running with just the SELECT permissions on the data tables involved. Although this is still not ideal from a security perspective, it is better than having to give them db_owner. I appreciate the feedback!

Thanks again,
Cat|||If security is a big issue, you could create a view based upon the table showing only the required columns and filtered rows, and then reference the view in your dynamic SQL. Then you can grant permissions on the view rather than on the table.|||Excellent suggestion.

Good advice is always worth repeating :cool:|||You could kludge your way around part of the problem using something like:DECLARE @.i INT

SET @.i = 1

SELECT o.name
FROM dbo.sysobjects AS o
ORDER BY
CASE @.i
WHEN 1 THEN o.name
WHEN 2 THEN Str(o.id, 20)
ELSE Convert(CHAR(30), crdate, 121)
END-PatP|||If security is a big issue,

I have to go change my pants now

"If"...good lord

Permissions Problem using Dynamic SQL

Hi all!

I've got a problem where I have created a stored procedure (using MS SQL Server 2000) that does a temporary table creation:

Code Snippet

CREATE #tmpData ( [some_fields] )

and then it uses dynamic SQL to populate the data

Code Snippet

SELECT @.ExecStr =
'INSERT INTO #tmpData
SELECT * FROM tData
WHERE [some_condition]
ORDER BY ' + @.SortColumn /* input parm to the SP */
EXEC (@.ExecStr)

I get a permissions error 229 when I try to run this because my user only has execute permissions for the stored procedure within the database. The only thing that I've found so far that will fix this is if I change the user's permissions to db_owner, which I don't want to do.

I've tried to explicitly grant permission within the stored proc, but since the object (the temp table) does not actually reside in the database, that gives me an error as well (4610: You can only grant or revoke permissions on objects in the current database.).

Is there anything else I can do? I really don't want to have to give the user that much freedom within the database, and removing the dynamic SQL really isn't a viable option either.

Thanks in advance for your help!

Cat

If you are using SQL 2000, you have no choice. To use dynamic SQL requires a high level of permissions.

IF you are using SQL 2005, explore the 'EXECUTE AS' property.

Refer to Books Online, Topic: 'EXECUTE AS'

Permissions on stored procedures & tables

At one client site, the DB server has 2 databases, that of my application
and that for another application. The client's consultant has added a
stored procedure which I am to access.
Signed onto my application's DB, I run the query:
Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
I did get an error saying my user wasn't valid on the other DB so I added it
& granted it Execute permissions on MyStoredProc. Now I get error messages
saying:
SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
'OtherUser'
I thought having the right to execute the stored procedure, I shouldn't need
explicit rights to the tables from which it selects.
What do I need to do here? Short of granting myself rights to a bunch of
tables in the other DB?
Thanks.
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown CompanyI'm not sure what the error with OtherOwner is or if you
ownership chains are intact. Even if they are, with SP3,
cross db ownership chains were introduced. They are off by
default for user databases. You can find more information in
the following article:
INF: Cross-Database Ownership Chaining Behavior Changes in
SQL Server 2000 Service Pack 3
http://support.microsoft.com/?id=810474
There is also information in the updated version of books
online under:
Cross DB Ownership Chaining
Using Ownership Chains
or online at:
http://msdn.microsoft.com/library/e...config_8d7m.asp
http://msdn.microsoft.com/library/e...curity_4iyb.asp
-Sue
On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
<d.wilson@.embtrak.com> wrote:

>At one client site, the DB server has 2 databases, that of my application
>and that for another application. The client's consultant has added a
>stored procedure which I am to access.
>Signed onto my application's DB, I run the query:
>Exec OtherDB.dbo.MyStoredProc Arg1, Arg2, Arg3
>I did get an error saying my user wasn't valid on the other DB so I added i
t
>& granted it Execute permissions on MyStoredProc. Now I get error messages
>saying:
>SELECT permission denied on object 'OTHERTABLE', database 'OtherDB', owner
>'OtherUser'
>I thought having the right to execute the stored procedure, I shouldn't nee
d
>explicit rights to the tables from which it selects.
>What do I need to do here? Short of granting myself rights to a bunch of
>tables in the other DB?
>Thanks.|||Thanks, Sue.
Those, especially the last link, explain the problem. This underscores the
recommendation to let DBO own all objects. Since that's not the case in this
DB, I have to give my user explicit permissions on each view & table.
dwilson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> I'm not sure what the error with OtherOwner is or if you
> ownership chains are intact. Even if they are, with SP3,
> cross db ownership chains were introduced. They are off by
> default for user databases. You can find more information in
> the following article:
> INF: Cross-Database Ownership Chaining Behavior Changes in
> SQL Server 2000 Service Pack 3
> http://support.microsoft.com/?id=810474
> There is also information in the updated version of books
> online under:
> Cross DB Ownership Chaining
> Using Ownership Chains
> or online at:
> http://msdn.microsoft.com/library/e...config_8d7m.asp
> http://msdn.microsoft.com/library/e...curity_4iyb.asp
> -Sue
> On Wed, 23 Feb 2005 18:53:53 -0500, "Daniel Wilson"
> <d.wilson@.embtrak.com> wrote:
>
it[vbcol=seagreen]
messages[vbcol=seagreen]
owner[vbcol=seagreen]
need[vbcol=seagreen]
>|||I feel that the procedure you execute first have only static query and the
second query has dynamic query, and the user you are using is supplied with
execute permission alone at this senario if you are trying to execute the
procedre with dynamic sql it will not work and will thro a error as SELECT
permission denied on object 'table name', database 'database name', owner
'owner name'. So, get the Select permission will solve the problem or modify
the sp by avoiding dynamic query.
"Daniel Wilson" wrote:

> Thanks, Sue.
> Those, especially the last link, explain the problem. This underscores th
e
> recommendation to let DBO own all objects. Since that's not the case in th
is
> DB, I have to give my user explicit permissions on each view & table.
> dwilson
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:14gq1111ktsv5nmuli5oi013ahtnqbvudr@.
4ax.com...
> it
> messages
> owner
> need
>
>

Permissions on stored procedures

Using Server Management Studio Express and SQL Server 2005 Express - is it possible to assign Exec permissions for users on a sproc by sproc basis. If so, how do I do this?

TIA

--
Mike BrindOk - I've found out how to do it. Just run a GRANT command in the Query Pane on the chosen procedure for the selected user.
sql

Permissions on a new stored procedure

Help!

I usually use SQL 2000 at work but upon deciding to work from home have installed and setup SQL express 2005. I use the management studio to write table and sps but for new sp I cannot find how to allow permission. I have been able to allow permission for all sps on the database I restored that I am now working on and have successfully allocated permissions to a new table but cannot do the same with the sp. The sp is viewable in the database but permission is denied when attempting to execute via my ASP script.

Any ideas?

Error Type:
Microsoft SQL Native Client (0x80040E09)
EXECUTE permission denied on object 'procBannerSlotList', database 'HotLizardWebsite', schema 'dbo'.

It seems after all this time I've managed to work it out for myself - so for anyone out there not aware of the solution - the solution is this:

In the database security - right click the role you wish permissions to be set for. Go to the Securables section, click Add and just add all objects belonging to dbo - I then just removed the dt prefixed objects that were not relevant - sp permissions now fully operational

Permissions on a Database

I have a user that need to create stored procedures but as the dbo account and not his own account so that the stored procedure is called dbo.storedprocedure and not domain\user.storedprocedure. He is a database owner but in order to have this happen I have to have him in the local Server Administrator group. What have I done wrong?

Also, he need to be able to run Enterprise Manager and SQL Ananlysis manager but I do not want him to be a local administrator but they will not start if he is just a local user. How can I accomplish it.

Thanks,

Stryder :confused:First of all, uninstall Enterprise Manager from his workstation.

All the user will need is db_owner permissions on the database. If he uses scripts, he can issue the create procedure command with the proper name (including owner) of the procedure:

create procedure [dbo].[someproc]
as
...

I do not know if this is possible in Enterprise Manager, as I never use EM to create procedures.

As for Analysis Manager, this is a bit thornier. There is a special local group on the Analysis Services Machine called Olap Administrators. Only members of this group can use Analysis Manger. The catch is that it is pretty much a binary permission. Either you are an Olap Administrator, or you are a simple user. No in between.

Hope this helps.sql

permissions needed for executing stored procedures

We want to create a SQL login say user1 and the only privilges we want to
grant it is to be able to execute stored procedures in that database.
So is it just good enough to just do the following ?
Grant exec on sprocx to user1
Does this take care of conditions that include DMLs ( insert,updates,selects
and deletes) that are within the stored procedure ? What about creating temp
tables,etc. ?
Thanks
Yes, doing that is possible, if the owner of the stored procedure (other
than user1 in your example) is also the owner of the tables to do the INSERT,
DELETE, etc.
Take a look at Ownership Chains in BOL.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs ( insert,updates,selects
> and deletes) that are within the stored procedure ? What about creating temp
> tables,etc. ?
> Thanks
>
|||Hassan (hassan@.test.com) writes:
> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs (
> insert,updates,selects and deletes) that are within the stored procedure?
Yes, provided that the tables and the procedures have the same owner.
And provided that you don't engage in dynamic SQL.
Also beware that if your stored procedures goes beyond INSERT, UPDATE,
DELETE and SELECT, granting execution rights to the procedure is not
sufficient. However, SQL 2005 offers mechanisms that permit you to address
this. I have an article on by web site that discusses this in detail:
http://www.sommarskog.se/grantperm.html

> What about creating temp tables,etc. ?
Any user have the permission to create temp tables, stored procedures or
not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx