May 18, 2005 at 12:38 pm
I have a stored procedure that drops a table and then creates (re-creates) the table. How do I go about:
1) Grant permission to drop the table (and then revoke it)
2) Grant permission to create the table (and then revoke it)
3) Assign select permissions to the created table
Thank you for your help. I am relatively new at this. It is 1) that I'm having the problem with.
Brenna
May 19, 2005 at 8:33 am
Are you trying to grant permissions from within the stored procedure? If so you wil run into a problem because a user/role that has permission to grant (or revoke) those rights will also have rights to create or drop the table and assign permissions to other users.
Another item to consider is what is the name that you wish to add the table under. If you wish to add the table as dbo, the user will need to be qualified as dbo and the table name in the create statement will need to be qualified with [dbo].[tablename], otherwise the table will be created in the db as [username].[tablename] and any other user that uses it (after rights have been granted) will need to fully qualify the name.
May 19, 2005 at 9:04 am
Hi,
You're right. It seems like a catch-22. I was trying to grant permissions in the stored procedure but the user/role doesn't have the rights to assign permissions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply