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?