User needs to create index on dbo owned table

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

  • 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

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

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

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

  • 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