[dba-SQLServer]Stored Procedure permissions question

Joe Rojas JRojas at tnco-inc.com
Wed Dec 10 12:24:30 CST 2003


Thanks for the reply.

This is kind of what I thought with regards to sprocs.
I don't know if you read by previous post but I am having a problem where I
have one database (DB1) that has a sproc that performs a SELECT statement on
a table that is located in another database (DB2). Both databases are on the
same server.

The user has database access permitted on both databases (DB1 and DB2).
The user has execute permissions on the sproc in DB1 (no rows are returned
from this sproc, just an OUTPUT value).
The user has no permissions declared (all checkboxes are cleared) on the
table in DB2.
An error of "SELECT permission denied on object 'tblLogins', database
'TNCOGlobal', owner 'dbo'" is received.

But if I use the same sproc (meaning I created another sproc with the same
code) and setup as above but in another database (DB3), everything works
perfectly.

I am trying to hunt down what would cause this.

Any ideas?

JR


-----Original Message-----
From: Arthur Fuller [mailto:artful at rogers.com]
Sent: Wednesday, December 10, 2003 2:43 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Stored Procedure permissions question

I could be wrong, but I think that a sproc doesn't have a special user,
but rather that in theory a sproc can do anything. The issue is outside
and above the sproc, so to speak -- at the level of user rights and/or
role permissions. Then your app logs in as a user, perhaps with a role,
and that dictates what happens. A user with no rights won't be able to
execute the sproc.

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Joe
Rojas
Sent: Wednesday, December 10, 2003 8:06 AM
To: 'dba-sqlserver at databaseadvisors.com'
Subject: [dba-SQLServer]Stored Procedure permissions question


Hello All,

(SqlServer 7.0)

I was wondering. If a user is granted execute permission to a stored
procedure (SP) and that SP accesses a table in a different database,
what user or login is used to access this table if the user executing
the SP has no explicit permissions to the table?

Another way of asking my question would be: We grant exec permissions to
SPs so that no user has direct access to a table. But is essence the
user is still getting data from a table, just through an SP. Does the SP
have it's own 'special' user to access the table directly?

Thanks!

JR





This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please
notify the sender as soon as possible and destroy this message. While
TNCO, Inc. uses virus protection, the recipient should check this email
and any attachments for the presence of viruses. TNCO, Inc. accepts no
liability for any damage caused by any virus transmitted by this email.
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please notify
the sender as soon as possible and destroy this message. While TNCO, Inc.
uses virus protection, the recipient should check this email and any
attachments for the presence of viruses. TNCO, Inc. accepts no liability for
any damage caused by any virus transmitted by this email.


More information about the dba-SQLServer mailing list