Arthur Fuller
artful at rogers.com
Wed Dec 10 18:37:47 CST 2003
I haven't tried that, Susan, but are you quite sure about this? It doesn't sound right. My guess is that you have several choices, the most useful two being: A) write a table udf in the common db that returns the relevant info from the users table; B) create a view within the common db that does the same thing. Deny table permissions to all mere mortals on the common db. Grant mere mortals access only to the udf or the view. Should a mortal be allowed to update his password etc., then write a sproc dedicated to that purpose and grant exec to it as well. Don't use an updatable view or dynamic SQL for this or you'll have to spend time defending against SQL injections. If it's a sproc most injections are trapped automatically. Arthur -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan Geller Sent: Wednesday, December 10, 2003 11:57 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]Stored Procedure permissions question We do what you are trying to do and we have found that in order to get a sproc in DB 1 to access a table in DB 2 we have to grant select access to the table in DB 2. I'm curious what you are doing in DB 3 that is making it not require this. Another thing we have found with permissions is that even if we are in only one database, if a sproc runs a query directly, then we don't need to grant rights to the underlying tables. However, if a sproc builds a sQL string and then exec(MySQLSTring) at the end, we do need to grant select access to the underlying tables. We only do this when absolutely necessary b/c it makes for less efficient sprocs. --Susan -----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 12:25 PM To: 'dba-sqlserver at databaseadvisors.com' Subject: RE: [dba-SQLServer]Stored Procedure permissions question 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. _______________________________________________ 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