How can I check if my user has create index permission?

Question

I'm working on a .NET library that is used with a third-party product I support. My library may be used with many different versions of the product. I don't control the schema of that product, but I can safely add additional indexes on standard tables. My library is installed into other projects in such a way that we decided it needs to bootstrap itself at startup, rather than counting on whoever uses it to perform additional install steps.

My library has some startup code that depends on data from a standard table. Some versions of the product are missing an index which, when missing, makes my library's startup painfully slow.

Some versions of the product do include the index, but the name of the index may vary. I've already worked out how to detect if there is an index on the table that includes exactly the fields I want or not. I also know how to create the index I want when it's not present. (Maybe because this is the very first run, or maybe because someone dropped it.)

So I know whether to create the index, but I don't know if my connection has the permissions to do so. I don't control the connection string -- I just use whatever is provided to me.

select @hasIndex = 1
    where {table has an index on the column I want}

if @hasIndex = 0
begin
    if {magic here: user has permission to create indexes}
    begin
        exec ('create index ix_TableName_FieldName on TableName(FieldName)');
    end
end

I could try to create the index and just eat the error if it fails. My .NET app can ignore the problem and continue, but it will still show up in the SQL errorlog, which could raise unwarranted concern. Besides, it feels sloppy. There's gotta be a good way to not try something that is going to fail, right?

Apologies if this is a duplicate. I searched around quite a bit. The nearest match I found was about granting permissions (one time, manually) to the user in order to restore a database. I'm trying to check permissions (each startup, unattended) to keep things in line going forward.

Answer 1

You can use the sys.fn_my_permissions function to see if your user has ALTER on the table.

IF EXISTS(select * from sys.fn_my_permissions('dbo.thetable', 'OBJECT')
              WHERE permission_name = 'ALTER')
    BEGIN
        PRINT 'I have ALTER'
        --CREATE INDEX STATEMENT HERE
    END