Sql-server – Get the list of all Tables, Views, Stored procedures that are not added in a publication for replication

Ugh, 2000.

Assuming all objects are owned by dbo, you can generate a script, e.g.:

SELECT N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type IN (N'U', N'V');

SELECT N'GRANT EXECUTE ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'P';

Now you can copy and paste the output in case you want to exclude any entries or validate. If you just want to blindly execute, you can instead do this, but it relies on the content of any individual set of commands being < 4K:

DECLARE @sql NVARCHAR(4000) = N'';

SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'U';

EXEC sp_executesql @sql;

SET @sql = N'';

SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'V';

EXEC sp_executesql @sql;

SET @sql = N'';

SELECT @sql = @sql + N'GRANT EXECUTE ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'P';

EXEC sp_executesql @sql;

Not sure if you have checked this; but there is an option in sp_addarticle called @identityrangemanagementoption. If set to manual, then it "Marks the identity column using NOT FOR REPLICATION". Pls check if this is the case.

Ref : https://msdn.microsoft.com/en-us/library/ms173857(v=sql.110).aspx