Printable View
Sorry I couldn't come up with a title that encapsulates my question so....
I don't think this is possible but figured it was worth a try:
Is it possible for a user to run a stored procedure that drops a table when they themselves don't have permission to drop that table?
Cheers.
it will run if you have permission onthe stored procedure but it will prompt an error
Are you really sure about that?Quote:
Originally Posted by mikee_phil
When executing a stored procedure on SQL Server 2000 you execute it with the permissions of the owner. If the owner has permissions to delete the table then there should be no problem for you to execute the procedure that deletes that table even though you don't have explicit permissions to delete it.Quote:
Originally Posted by Fishcake
On SQL Server 2005 it is different. You can define whos permsissions should be used when executing a stored procedure.
So, since all our SPROCS are owned by DBO - they have full rights to do whatever they want - right?
We only ever give EXEC permission to users for our SPROCS...
Yes. User permissions are overridden by stored procedures, even explicit DENYs, but only within the database where the sproc exists. If the sproc accesses data from another database,n then the user will need explicit permissions to these data.Quote:
Originally Posted by szlamany
No, not whatever they want (they being the stored procedure). You cannot execute statements, on behalf of users, that create database items (like Tables, Rules, Views) or statements that are only granted to predefined roles (such as Drop Table).Quote:
So, since all our SPROCS are owned by DBO - they have full rights to do whatever they want - right?
You are right and I am wrong :blush:. Permissions defined by predefined roles, like DDL rights, are not inherited, so CREATE and DROP statements would require the user to be member of the db_ddladmin role.Quote:
Originally Posted by brucevde
Permission inheritance are explained in BOL if you look at "ownership chains".