MS SQL Server - Replication and read only

Author

Replication and read only

Massa Batheli

2006-10-24, 6:31 pm

There is a need for a read only reporting server because the production
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)

I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions

Your input as usual will be highly appreciated

Paul Ibison

2006-10-24, 6:31 pm

Massa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Massa Batheli

2006-10-24, 6:31 pm

Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users

Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Hilary Cotter

2006-10-24, 6:31 pm

Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"Massa Batheli" <mngong@gmail.com> wrote in message
news:1159184643.454594.323960@i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
>
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
>
> Your input as usual will be highly appreciated
>

Paul Ibison

2006-10-24, 6:31 pm

The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:

SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)

Then:

sp_addrolemember 'db_datareader', 'yournewrole'

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

amish

2006-10-24, 6:31 pm

Massa Batheli wrote:
[color=darkred]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
>
> Paul Ibison wrote:

Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .

Regards
Amish shah
http://shahamishm.tripod.com

amish

2006-10-24, 6:31 pm

Sponsored Links