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:
>

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...
>
>
|||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...[vbcol=seagreen]
> 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:
that[vbcol=seagreen]

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 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 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 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

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. ?
ThanksYes, 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

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. ?
ThanksYes, 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,selec
ts
> and deletes) that are within the stored procedure ? What about creating te
mp
> 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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Permissions for Stored Procedures generated by VS 2005

I have a DataSet (Data Component in Beta 1) and I want to add Fill andGet methods by using a Stored Procedure that was created by VS 2005 (aspnet_Membership_GetAllUsers). I probably need to use Enterprise Manager to do so but I am not sure what permissions I need to set and how to set them.

Stored Proc permissions are DBO(database owner). Hope this helps.

Permissions

Hi All
I would like to run a job that checks for any stored procedures without
PUBLIC access and grant them public access. What would be the best way to d
o
this and which tables/sp do i have to use? Thank you in advance.If you are using SQL Server 2005 you can do that easily with only this
command:
grant execute on database::mydb to public
Ben Nevarez, MCDBA, OCP
Database Administrator
"Baldy" wrote:

> Hi All
> I would like to run a job that checks for any stored procedures without
> PUBLIC access and grant them public access. What would be the best way to
do
> this and which tables/sp do i have to use? Thank you in advance.

Permissions

If a person needs to see, modify, create, and execute stored procedures and
DTS Packages on SQL server, what would be the minimum set of permissions
using server roles or database roles to allow this to happen?
Message posted via http://www.droptable.com
2000: sysadmin.
2005: sysadmin or GRANT ALTER TRACE TO ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert R via droptable.com" <u3288@.uwe> wrote in message news:570fb034e80e6@.uwe...
> If a person needs to see, modify, create, and execute stored procedures and
> DTS Packages on SQL server, what would be the minimum set of permissions
> using server roles or database roles to allow this to happen?
>
> --
> Message posted via http://www.droptable.com

Permissions

If a person needs to see, modify, create, and execute stored procedures and
DTS Packages on SQL server, what would be the minimum set of permissions
using server roles or database roles to allow this to happen?
Message posted via http://www.droptable.com2000: sysadmin.
2005: sysadmin or GRANT ALTER TRACE TO ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert R via droptable.com" <u3288@.uwe> wrote in message news:570fb034e80e6@.uwe...eagreen">
> If a person needs to see, modify, create, and execute stored procedures an
d
> DTS Packages on SQL server, what would be the minimum set of permissions
> using server roles or database roles to allow this to happen?
>
> --
> Message posted via http://www.droptable.com

Permissions

If a person needs to see, modify, create, and execute stored procedures and
DTS Packages on SQL server, what would be the minimum set of permissions
using server roles or database roles to allow this to happen?
--
Message posted via http://www.sqlmonster.com2000: sysadmin.
2005: sysadmin or GRANT ALTER TRACE TO ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message news:570fb034e80e6@.uwe...
> If a person needs to see, modify, create, and execute stored procedures and
> DTS Packages on SQL server, what would be the minimum set of permissions
> using server roles or database roles to allow this to happen?
>
> --
> Message posted via http://www.sqlmonster.com

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
QuentinAlthough you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
>|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
permissions[vbcol=seagreen]
can[vbcol=seagreen]
>|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...
tables.[vbcol=seagreen]
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
tables.[vbcol=seagreen]
> the
> permissions
> can
>

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
Quentin
Although you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>
|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
>
|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
permissions[vbcol=seagreen]
can
>
|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
tables.[vbcol=seagreen]
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
tables.
> the
> permissions
> can
>

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
QuentinAlthough you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > Hi group,
> >
> > is there a way to create a role, assign permission to it so that it can
> > create procedures with dbo as owner, and not able to drop tables?
> >
> > Quentin
> >
> >
>|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > Although you can't create a user role for this, you can add a user to
the
> > db_ddladmin fixed database role and then DENY those statement
permissions
> > you don't want the user to have For example:
> >
> > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > DENY CREATE TABLE TO MyUser1
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Quentin Ran" <ab@.who.com> wrote in message
> > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > Hi group,
> > >
> > > is there a way to create a role, assign permission to it so that it
can
> > > create procedures with dbo as owner, and not able to drop tables?
> > >
> > > Quentin
> > >
> > >
> >
> >
>|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > thanks for the response. I have been there -- you can still drop
tables.
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> > Dan,
> >
> > thanks for the response. I have been there -- you can still drop
tables.
> >
> > Quentin
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > > Although you can't create a user role for this, you can add a user to
> the
> > > db_ddladmin fixed database role and then DENY those statement
> permissions
> > > you don't want the user to have For example:
> > >
> > > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > > DENY CREATE TABLE TO MyUser1
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Quentin Ran" <ab@.who.com> wrote in message
> > > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > > Hi group,
> > > >
> > > > is there a way to create a role, assign permission to it so that it
> can
> > > > create procedures with dbo as owner, and not able to drop tables?
> > > >
> > > > Quentin
> > > >
> > > >
> > >
> > >
> >
> >
>

permission denied on object 'sp_sdidebug', database 'dbname',

Hi.
I have seen lots of discussion.
My starting problem was that after installing sql server 2000 and sp3, I cou
ld
not debug stored procedures.
I thought I saw the solution in MSDN, but
it did not work for me (I could not grant execute
on sp_sdidebug to my user).
short end is.. I had to do something else.
I'm developing on a local mssqlserver 2000 sp3
windows 2000 is the o/s
I logged in as the sp and ran this:
EXECUTE sp_sdidebug 'LEGACY_ON'
Then I tried to enter this:
GRANT EXECUTE
ON sp_sdidebug
TO MYUSER
But I got an error about not being able to set permissions for objects owned
by others.
I'm obviously clueless, but I did find a blurb from someone who said they
had to add the users to the MASTER database as users with db_owner checked.
After I did this, I could debug sp's, but still could not do the 'GRANT
EXECUTE ON sp_sdidebug'.
So
1 - what is the best way to get the (EXECUTE sp_sdidebug 'LEGACY_ON')
executed each time the db starts up? (if I need to put it in some sp, how
exactly do I get that called at startup?)
2 - any idea what I'm boffing here to prevent the 'GRANT EXECUTE ON
sp_sdidebug' from working?
3 - any thoughts about the MASTER.db_owner "solution"?
Thanks for your patience as I am sure this is a well trampled ground.
Jeff KishJeff,
Why do you need to GRANT execute on this sp?
AMB
"Jeff Kish" wrote:

> Hi.
> I have seen lots of discussion.
> My starting problem was that after installing sql server 2000 and sp3, I c
ould
> not debug stored procedures.
> I thought I saw the solution in MSDN, but
> it did not work for me (I could not grant execute
> on sp_sdidebug to my user).
> short end is.. I had to do something else.
> I'm developing on a local mssqlserver 2000 sp3
> Windows 2000 is the o/s
> I logged in as the sp and ran this:
> EXECUTE sp_sdidebug 'LEGACY_ON'
> Then I tried to enter this:
> GRANT EXECUTE
> ON sp_sdidebug
> TO MYUSER
> But I got an error about not being able to set permissions for objects own
ed
> by others.
> I'm obviously clueless, but I did find a blurb from someone who said they
> had to add the users to the MASTER database as users with db_owner checked
.
> After I did this, I could debug sp's, but still could not do the 'GRANT
> EXECUTE ON sp_sdidebug'.
>
> So
> 1 - what is the best way to get the (EXECUTE sp_sdidebug 'LEGACY_ON')
> executed each time the db starts up? (if I need to put it in some sp, how
> exactly do I get that called at startup?)
> 2 - any idea what I'm boffing here to prevent the 'GRANT EXECUTE ON
> sp_sdidebug' from working?
> 3 - any thoughts about the MASTER.db_owner "solution"?
> Thanks for your patience as I am sure this is a well trampled ground.
> Jeff Kish
>|||When I installed ms sql server with sp3 I found I could not run the debug
on stored procedures. It was grayed out.
Once I did some research, I determined (I "think" from MSDN) that
I needed to GRANT to run the debugger logged in as this user.
I got an error doing the GRANT. Looking around a bit more, I saw that
I also needed to run in legacy mode for this object.
I submitted a script for that, but I still could not GRANT.
After substantially more investigation, I saw one user was able to get the
debugger running if he added his user as a db_owner to the master
database.
After I did this, I still could not do the GRANT, but was able to run the
debugger on stored procedures.
Now I'm trying to figure out if what I did was a reasonable solution, and
why I could not do the GRANT. Finally, I'm casting about for an explanation
and some level of confidence for what I did to "fix" the problem.
Also, I wanted to figure out how to run the LEGACY script at startup so I
don't need to do it every time.
Thanks
On Mon, 28 Feb 2005 16:49:04 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>Jeff,
>Why do you need to GRANT execute on this sp?
>
>AMB
>
>"Jeff Kish" wrote:
>
Jeff Kish