SQL server running stored proc as different user

[Fishcake]

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.

[mikee_phil]

it will run if you have permission onthe stored procedure but it will prompt an error

[szlamany]

it will run if you have permission onthe stored procedure but it will prompt an error

Are you really sure about that?

[kaffenils]

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.

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.

On SQL Server 2005 it is different. You can define whos permsissions should be used when executing a stored procedure.

[szlamany]

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

[kaffenils]

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.

[brucevde]

So, since all our SPROCS are owned by DBO - they have full rights to do whatever they want - right?

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

[kaffenils]

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

You are right and I am wrong . 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.

Permission inheritance are explained in BOL if you look at "ownership chains".