.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Replication and database roles

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

Hi all!

We have replication set up between 2 servers (SQL 2005) and among tables and views we also replicate procedures. Because roles can't get replicated, we have roles with procedure execute permissions set up on Subscriber server. It works fine until we add procedure at Distrubutor server and reinitialize the subscription. Then database roles get "reset" i.e. all procedure entries are removed. Table entries are left intact in DB role.

Can someone tell me if this is functioning as designed or is something misconfigured. If database role can't get replicated, then it would be nice if they could at least not be replicated.

I would appreciate any help or to be pointed in right direction.

Kind regards, Max

View Complete Post

More Related Resource Links

How to stop the repeated database queries for roles


Hello, friends,

We have a web application using VS 2008, c#. We try to filter siteMap nodes based on security roles. We have our customized the mether GetRolesForUser() in RoleProvider class to determine a user's role. In this method, roles will be returned by querying an SQL Server database.

However, we found that each time a page was loaded/refershed, this  GetRolesForUser() was called, and the database would be queried. This is too MUCH and expensive.

We thought the roles should be queried only once when a user logs in. After that, role info should be stored somewhere for this user, rather than query DB all the time.

Any ideas, reference paper, snipet,...,?

Thanks a lot!

Tx Replication Error when renaming Subscription Database

Tx Replication Errors when renaming Subscription Database. Please help. It seemed to work earlier, but now is giving errors when i rename subscription database.

how to do sharepoint database disk usage analysis and selective replication?



I have a SharePoint 2007 database that is 16GB in size and I want to know why, and how I can reduce the size. Ideally I would like a trimmed replica to use as a developer workstation that retains a good sample data set, and has the ability to be refreshed.

Can you please tell me if there are any third party tools or other methods to accomplish this? I have found the Microsoft tool (stsadm) to be very limited in this regard.

Many thanks.

How to verify Master and Replication database are in synch



I have two SQL Server 2005 standard Edition SP2. Where one server act as master database and other as replication database.

I have setup transactional replication between master and replication database. also i have written one stored procedure which check the total number of the records in master and replication database.

Now my requirement is to verify data between two server are in synch.

Let me explain with example:

I have customer table in master database has customerid:100  & customer_City:New York.

User update city to Mumbai

Now I have to verify at one point in time, may be at the end of day that  customer_city of customerid:100 in replication database  updated to Mumbai. In short, both master and replication databases are in synch.

Please let me know your view.


Thanks and Regards





A little bug with WebMatrix Sql ce Database , WebSecurity and Roles ?


Hi , i'm reviewing heavily the api provided with webpages which is really cool , it reminds me the api zend provides for php.

But i add some issues with WebSecurity and the Roles object.

I think you should not be able to insert multiple times the same role to the same user with the Roles.AddUserToRole(user, role) command cause it makes the database editor bug big time. I could not edit the webpages_UsersInRoles table manually anymore because of identical rows in the Data editor. i had to do a SQL DELETE to be able to edit the table again. So maybe there should be some kind of check if the association exists before inserting another user/role relationship.

I wasn't able to use some methods of simpleroleprovider either. Is it fully implemented?

Anyway , i cant wait for the official stable release.


Move the database files to different branches before replication?


Hi all,


I want to start a replication between 3 sites using Merge replication, 1<sup>st</sup> branch is the distributer and publisher and already has a database, the others are subscribers and they are new (No data yet). But the size of the database in the main branch is about 4 GB. So if I start the replication, 4 GB will be replicated which something I don’t want. Is there a way I can put the database in the other branches manually? So nothing will need to replicate at the first time. And then replication will take place.


Waiting your valuable reply,




assigning database roles with smo (C#)


Hi All,

I'm using VS C# express with SQL 2005 express.

I have the following problem:

I would like to assign database roles in a given database for a login by using smo. Let's say:

I have a Login, created with smo named MyLogin and a database name MyDatabase.

How can I map MyLogin to MyDatabase and give for example db_datawriter database role membership for it, using SMO?

I do not find the way, propably I miss something...

Any help is highly appreciated, I would be thanksfull if one could give a code example to solve this.




Bottelnecks faced,When trying to implement CDC and Transctional Replication on the same Database


Hi Guys,

When I tried implementing the CDC (first) and Configured Transctional Replication on the same database.. the CDC at the database level looks good/unchanged ..but CDC at the table level is disabling,Replication is changing the metadata tables of CDC..

So, Could you plzz suggest me on thiss……How to implement CDC and Transctional replication on the same database

Anil Inampudi

HowTo: Replication to a mostly disconnected database


Searches have not turned up an answer to this:

Set up Transactional Push Replication to a mostly disconnected Subscriber. Publisher/Distributor is SS2008 R2, the Subscriber will probably be SS2008 or 2008 R2. How do you get the Distributor to initiate and maintain a VPN connection (talking nuts and bolts here folks) to the disconnected network that contains the Subscription database so that it can push the publication/transactional data to the subscriber?

Will a ROLLBACK in a subscriber database trigger cause the replication engine to abort?


We have some data retention policy deletes for a table in the publisher database that we do not want to be replicated to the subscriber database.  Our method of implementation is to write a delete trigger for the table in the subscriber database that intercepts the deletes by checking the date associated with the deleted data, and if the date is < 2 months ago we execute a ROLLBACK in the trigger.  The ROLBACK is effective and we get the desired results, but this however produces the message:

    Msg 3609, Level 16, State 1, Line 2 

    The transaction ended in the trigger. The batch has been aborted.

Is this message a problem for replication?  Will the replicaton engine abort?  Is this just an informational message, a warning message, or error message?

Insert into merge replication database issue...


I've been fighting with merge replication for the past few days over the same hurdle and I can't seem to figure this one out.  I create the publication that publishes 36 articles (28 of which are download only).  After creating the publication, I can replicate perfectly normal as expected.  When replicating, I pass in a project number through the host_name() variable and filter the data based on that value and all works great.  I've replicated about 10 different projects, modified data, and merged the changes back without issue.

Where I'm running into an issue is when I add a new project to the mix.  I have an SSIS project that takes data and puts the data into a staging area.  From there, I have an ETL that pulls the data from the staging area and inserts it into the the database that is set up with merge replication.  Being that these new records will need to be replicated in the future, I do a simple insert into, select from so that the replication triggers are fired and these rows are tracked.

After the data load (~150,000 rows in total among 5 tables), I go to replicate this new project however it never gets past the step

"Retrieving subscription information."

It eventually times out and never transfers the data.  When I go back and try to rep

Transactional Replication with Database reindexing

We are using SQL 2005 and are setting a large database for transactional replication. If the publishing database is reindexed using a Maintenaince Plan that reindexes all tables, will the commands for reindexing be sent to the subscriber as well?

one database logshipping, replication


Hi Team,

Is it possible to create Replication and logshipping only 'xxx' one database...


in Sql server 2005 or 2008


in replication use to database level security..

in logshipping server level but is it possible to create two disaster only one database.





Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

MS SQL Server: Disconnect Users From Database - Kill User Session

If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.

Publishing SQL Server Database using Publishing Wizard : Tips & Tricks

We can use SQL Server Publishing Wizard to deploy our local Database to remote hosting server/production server.This feature is available in SQL Server 2008/VWD 2008.

Below is the complete step by step guide of this process.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend