Minimum permissions for alter table

Posted on 2011-10-13
Last Modified: 2012-05-12
Hi Folks

I have a web based .net 4 app which allows admin users to issue commands which add columns to tables.

When I try "alter table" with the usual default app permissions they fail.

So I grant "alter" to the user on the table, then it fails with a different error message - "sqltransaction has completed it is no longer usable"

I tried changing the users default schema to be "dbo", which is where the tables are, but same problem.

But if I make the user a member of the sysadmins role, it works fine.

Any ideas what the minimum permissions are ? I don't really want to make customer's sysadmin users!


EDIT: This is running on Sql 2008 R2. Web edn..
Question by:plq
    LVL 11

    Accepted Solution


      ALTER permission is enough. See the MS documentation

    "sqltransaction has completed it is no longer usable" -- this is different issue. Check your code I mean opening the connection and dispose state.
    LVL 8

    Author Comment

    Hmm I followed your advice and you are right it was not alter permissions. The code does this on views that reference the table being altered:

    "exec sp_refreshview " & viewname

    I understand this is necessary to ensure that any views that used "select *" get updated. (in the absence of schemabinding)

    This is not in itself bad, the above line of code is wrapped in a try catch with no processing of the exception, so this error is ignored.

    But its this exec that is messing up the transaction.

    Looking at the MS source  of sp_refreshview ...

    ALTER procedure [sys].[sp_refreshview]
        @viewname      nvarchar(776)
          declare @ret int
          exec @ret = sys.sp_refreshsqlmodule_internal @viewname, N'OBJECT', 1 -- viewonly
          return @ret

    ... its referring to an internal sp which is not listed in the master sp list.

    However its obvious that that sp is raising an error and executing a rollback. Firstly I think its pretty nasty that MS have coded it this way - if the exec sp_refreshview fails why trash someones transaction ?

    Looking at the docs for sp_refreshview I need to grant alter permissions to every view in the database. PITA !

    Will update here if that works.


    LVL 8

    Author Comment

    it worked.. thanks

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now