SQL Server Performance

Modification of a table scheme causes a table removed from a database role

Discussion in 'SQL Server 2008 General DBA Questions' started by WingSzeto, Mar 13, 2009.

  1. WingSzeto Member

    We define a database role which has a list of tables and a few SQL users who have access to these tables. From time to time, a table would drop out from that database role without anyone doing the actual removal. It seems whenever this happens, that table was modifed by either making table changes (i.e field changes, etc) or removing the table from replication. This problem also happens in SQL 2005. Does anyone encounter this problem before? We are on SQL 2008 std CU3 and SQL 2005 std sp2.
    Any pointers on this is very much appreciated.
  2. melvinlusk Member

    The is the table owner changing when the schema changes are taking place?
  3. WingSzeto Member

    No, the table ownership didn't change.
    Is this one of the known situations that will cause a table removed from a database role?
  4. satya Moderator

    Is this occuring intermittently or during any replication process?
  5. WingSzeto Member

    I just did another table schema change by modifying the varchar field size from 30 to 255. Here are what I did:
    1. run the sp_dropsubscription and sp_droparticle to remove the table that I want to make schema change
    2. Make the table schema change.
    3. Run the sp_addarticle and sp_addsubscription to add the table back to the replication.
    4. Run the snapshot to replicate the table to the subscriber dbs
    after I did the above, I check my user defined database role ('user' database->Security->Roles->Database Role->'user defined role') in the publisher db, where the above modified should be listed but as expected, it is gone again. I have to manually add the table back to my user defined database role.
    Is this a bug? Can you or someone duplicate that?

Share This Page