Server and Database role memberships required to add columns and constraints I have a user with the following server and database role memberships:


securityadmin (server)
public (server)
db_owner (database)
db_accessadmin (database)
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:


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?
What are the minimum requirements for this script to run through?
The only way I could get this script to run through on SQL 2008+ was to grant sysadmin membership, is that as expected?
First, the rollback transaction will undo what you are doing. Use commit transaction instead.


Yes
DB_owner in a database named XXX
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.