3

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.

  • 1
    The error log will not record if your user tries to create an index and fails. There would have to be other security auditing or trace logging in place for anyone to see that it happened. – Tony Hinkle May 24 at 17:21
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.