June 9, 2005 at 3:26 pm
Hi everyone,
We have a stored proc that creates drops and later creates some indexes on some tables that are owned by dbo. One of our users need to run this SP manually once in a while, but he cannot since he doesn't have permissions to create indexes on dbo owned tables. What are some creative ways that will allow this user to run the SP? Thanks in advance for your help.
June 9, 2005 at 4:02 pm
Hi,
2005 BOL says:
To execute CREATE INDEX, at a minimum, ALTER permission on the table or view is required. This permission is granted by default to the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles.
You will need to ask your DBA or users who are in db_owner group or db_securityadmin group to give this SP user ALTER permission on all relevant tables or make him/her a member of db_ddladmin
If they would not do that escalate to your manager
Yelena
Regards,Yelena Varsha
June 9, 2005 at 4:34 pm
Maybe you could create a small app that connects with a special account that his in the db_ddladmin group, and that he could then call that proc (and only that proc obviously).
It would be a security threat and he would need to have HIS login given too high priorities.
June 10, 2005 at 1:24 am
How about using application roles? Create an application role that is a member of db_ddladmin or at least is allowed to alter the table(s) necessary for the indexes. Then have either the client application set the application role before executing the proc, or even just let the proc itself set the application role.
June 10, 2005 at 5:38 pm
We are moving the "create index" statements to another stored procedure and which we will call from the original SP. I am not sure if this will work, but will let you know once I know.
June 10, 2005 at 5:52 pm
Personally, I would create a tiny app that did nothing other than those create/drop statements, and connected via a SQL Server login with the appropriate permissions. But then, I'm a programmer.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply