0

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:

BEGIN TRAN
IF NOT EXISTS (SELECT COLUMN_NAME 
               FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = 'Opfix' 
                 AND COLUMN_NAME = 'ModifiedBy')
BEGIN
    ALTER TABLE [OpFix] ADD [ModifiedBy] [int] NOT NULL
    CONSTRAINT DF_OpFixModifiedBy DEFAULT ([dbo].[fnSoftmarLoginUserID]())
END
ROLLBACK TRAN

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? – Marieke Smit Nov 4 '16 at 8:30
  • 1
    I have found my issue - the user was not in the db_owner role. – Marieke Smit Nov 4 '16 at 9:48
0

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, privacy policy and cookie policy

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