Learn how to a build a cloud-first strategyRegister Now


Minimum permissions for alter table

Posted on 2011-10-13
Medium Priority
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
  • 2
LVL 11

Accepted Solution

rajvja earned 2000 total points
ID: 36962840

  ALTER permission is enough. See the MS documentation http://msdn.microsoft.com/en-us/library/ms190273.aspx

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

Author Comment

ID: 36963234
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.



Author Comment

ID: 37094398
it worked.. thanks

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

810 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