I have a user with the following server and database role memberships:

  1. securityadmin (server)
  2. public (server)
  3. db_owner (database)
  4. db_accessadmin (database)
  5. db_securityadmin (database)

When running the below script in SQL 2005, it runs through without any issues:

               WHERE TABLE_NAME = 'Opfix' 
                 AND COLUMN_NAME = 'ModifiedBy')
    ALTER TABLE [OpFix] ADD [ModifiedBy] [int] NOT NULL
    CONSTRAINT DF_OpFixModifiedBy DEFAULT ([dbo].[fnSoftmarLoginUserID]())

When running the same script on SQL 2008 and SQL 2012, I get the below error:

Cannot find the object "Opfix" because it does not exist or you do not have permissions.

So, my questions are:

  1. Does it makes sense that I get different results between SQL 2005 and SQL 2008+, with the same update and the exact same user permissions?
  2. What are the minimum requirements for this script to run through?
  3. The only way I could get this script to run through on SQL 2008+ was to grant sysadmin membership, is that as expected?
  • I have also tried the server role 'dbcreator', but I received the same error. From the following definition of dbcreator: "Members of the dbcreator fixed server role can create, alter, drop, and restore any database." One would think that you should be able to alter the table? Nov 4, 2016 at 8:30
  • 1
    I have found my issue - the user was not in the db_owner role. Nov 4, 2016 at 9:48

1 Answer 1


First, the rollback transaction will undo what you are doing. Use commit transaction instead.

  1. Yes
  2. DB_owner in a database named XXX
  3. Do you have a "USE XXX" in your script? I normally remove that stuff to protect DB names and server names when posting. What you are saying here does not make sense to me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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