Fix : Msg 4928, Level 16, State 1, Line 9 Cannot alter column 'XXXXXX' because it is 'REPLICATED'

 Msg 4928, Level 16, State 1, Line 9

Cannot alter column 'xxxxxxxxxx' because it is 'REPLICATED'.  

I got this error when I tried to increase table column data type length from 150 to 300. I have removed the article from publication but no luck still error coming.

 

Finally the below procedure helped me to fix the issue :

This article had been previously removed from Two publications. So the above error should not have occurred...   We have a consistency issue. 

 Diagnostic  1: Check syscolumns.colstat.

 

Select * from syscolumns where id = object_id(<table name>)

The value of the colstat field for the particular column was 8192. I've run into this before on SQL 2k. The fix at that time was to update this field to 0 for the offending column. If you attempt that now, you get the following error :

Msg 259, Level 16, State 1, Line 2

Ad hoc updates to system catalogs are not allowed.

Either way... the 8192 is going to be an issue. I don't have the luxury of putting my production server into single user mode to modify system catalogs... so my fix needs to be real time and online.

Diagnostic 2: check sys.columns

Select is_non_sql_subscribed ,* from sys.columns where object_id = object_id(<table name>)

If the is_non_sql_subscribed field = 1, you have an issue. This can happen on servers with any type of replication including SQL Server only.  I'm currently running SQL 2k5 replication in my environment. No third party replication. Either way , the value returned for me was "1" which identifies an inconsistency issue. 

When the snapshot agent creates a new snapshot for your publication it sets this field to 1, once the snapshot is finished the agent resets the value back to 0. Our solution will use this snapshot agent behavior.

 

Workaround:

Create a new publication with your questionable article. Snapshot it. Check the data. SQL will attempt to fix your inconsistencies and properly flag the is_non_sql_subscribed field. Drop the article from your new publication, make your table edits and add the article back to your original publications.

Database Lelel Roles in SQL Server

Database Roles :

Role is used to group a set of privileges .

We can reduce the process of granting and taking back permissions to large no of users with help of roles.

SS supports 3 types of roles .

                 1. Fixed Database Roles .

                 2. Custom Database Roles.

                 3. Application Database Roles.

1. Fixed Database Roles :

There are 8 fixed database roles which comes along with SS database .

SS supports the following fixed db roles .

1. db_owner :

  Member of db_owner role can perform any task on the db.

2. db_ddladmin :

  Member of ddl_admin can work with DDL commands such as create , alter and drop .

3. db_securityadmin:

  Member of db_securityadmin can create users ,roles , schemas , certificates etc .

4. db_datareader :

  Member of db_datareader can read data from any object.

5. db_datawriter :

  Member of db_datawriter can work with insert , update and delete commands.

6. db_denydatareader :

  Member of db_denydatareador cannot work with any select command.

  No other users of db can grant the permission .

7.  db_accessadmin :

  Member of db_accessadmin can work with disk files.

8.  db_backupoperator :

  Member of db_backupoperator role can take backup of db and can restore db.


2. Custom Database Roles :

We can create role with required privileges .

steps to create custom database roles .

1. Creating Role

 Syn : Create Role <Role_Name>

2. Granting Permissions to the Role

Syn : Grant .... to  <Role Name>

3. Adding user to the role using sp_addrolemember


Requirement:

Create a role with the name customercare _role  Grant select , insert on emp table and also Grant Select on Library Schema and also Grant backup database permission .

Now add the user John to the above role .

Steps to achieve the above requirement

1. Creating the Role

        user test
           Go
create role CusomerCare_Role

2. Granting privileges to the role

         Use Test
            Go
Grant Select , Insert on Emp to CustomerCare_Role
Go
Grant Select on ::[Library] to CustomerCare_Role
Go
Grant Backup database to CustomerCare_Role
Go

3. Adding John to the Role

        Use Test
             Go
sp_addrolemember @rolename='CustomerCare_Role' , @member name='John'

My backup was failed what may be the possible scenarios.

In general backup will fail due to the below possible reasons

                                           * No Disk Space (Error No : 3271 ) ,

                                           * MSDB was offline.

                                           * SQL Agent Services was stropped.

                                           * Server was busy.

                                           * Net Work Problems.

How to give SQL Server Agent permissions to the users

Scenario:

  Whenever user requested to give SQL Server Agent permission then what you will do ?

Solution :

  3 roles are there in MSDB.

1. SQLAgentUserRole

       *Ability to manage the jobs that they own .

2. SQLAgentReaderRole

       *All of the SQLAgentUSerRole rights

       *The ability to review multiserver jobs i.e jobs configurations and its history

3.SQLAgentOperatorRole

       *All of the SQLAgentReaderRole rights.'
   
       *The ability to review operators , proxies and alerts.

       *Execute Start or Stop all local jobs

       *Delete the job history for any local job

       *Enable or Disable all local jobs and schedules

Ex:

use msdb
   go
create user username for login loginname

use msdb
    go
sp_addrolemeber @rolename='SQLAgentUserROle',@membername='UserName'