Server and Database role memberships required to add columns and constraints

Question

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?

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.