Server and Database role memberships required to add columns and constraints

0 votes

1 answer

305 views

sql-server sql-server-2008 sql-server-2012 sql-server-2005 alter-table

                          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?


                        

Asked by Marieke Smit (1 rep)

Nov 4, 2016, 08:21 AM
Last activity: May 8, 2025, 04:03 PM

Server and Database role memberships required to add columns and constraints

Related Questions