SnapShot Replication question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sqldba - 28 Apr 2006 21:42 GMT

I am going to create snapshot replication which will be scheduled to run
once in a month. my question is about as there are 10 users in my publisher
database, how i can create those 10 users in Subscribers. i know there are
options like i can create manually but i don't want to take that route. is
it possible that users are created when first snapshot is applied. This
replication is going to be on the same server where i will have PUB,DIST &
SUB. This replication is not from Server A to B.
As i applied my first SNAPSHOT, i see all information but not able to see
all users from PUB even though it is on the same server.Any help will be
appreciated.

Reply to this Message

Paul Ibison - 28 Apr 2006 22:33 GMT

You could use a pre-snapshot script to achieve this.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Reply to this Message

sqldba - 29 Apr 2006 02:11 GMT

Thanks for your help & reply. as i copied script from your website but i am
not sure what i have to replace in your script to run in my server. if you
please provide me some more details, i will really appreciate & be very
thankfull to you.
I am using this scripts:-

SELECT 'Grant ' +
case
when action = 193 then 'SELECT'
when action = 195 then 'INSERT'
when action = 196 then 'DELETE'
when action = 197 then 'UPDATE'
when action = 224 then 'EXECUTE'
end as Grant1,
...............................................
...............................
............................
.........................

Reply to this Message

Paul Ibison - 29 Apr 2006 09:33 GMT

My script is just for permissions, not for logins or users. For logins, have
a look at sp_helprevlogin (http://support.microsoft.com/kb/246133). For
users it's simply sp_grantdbaccess. You'll have to add logins, then users,
then permissons.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Reply to this Message

Hilary Cotter - 29 Apr 2006 02:30 GMT

what about using the DTS transfer logins task for this?

Signature

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

>I am going to create snapshot replication which will be scheduled to run
> once in a month. my question is about as there are 10 users in my
[quoted text clipped - 7 lines]
> all users from PUB even though it is on the same server.Any help will be
> appreciated.

Reply to this Message

sqldba - 29 Apr 2006 02:46 GMT

If i use DTS, then at the most User will be transfer but not SID.
To be honest with all respect i am very much confused with Mr. Paul Script,
which is as shown below.

/*****************************************************************************************
Created: 11/02/2005
By: Paul Ibison
Purpose: Script to produce publisher permissions for the subscription
'yyy':
******************************************************************************************/

SELECT 'Grant ' +
case
when action = 193 then 'SELECT'
when action = 195 then 'INSERT'
when action = 196 then 'DELETE'
when action = 197 then 'UPDATE'
when action = 224 then 'EXECUTE'
end as Grant1,
' ON [' + delivery.dbo.sysobjects.name + '] TO ' +
delivery.dbo.sysusers.name as Grant2
--,ss.srvname
--,delivery.dbo.syspublications.name AS Publication
FROM delivery.dbo.syssubscriptions
INNER JOIN delivery.dbo.sysextendedarticlesview ON
delivery.dbo.syssubscriptions.artid =
delivery.dbo.sysextendedarticlesview.artid
INNER JOIN delivery.dbo.sysobjects ON
delivery.dbo.sysextendedarticlesview.objid =
delivery.dbo.sysobjects.id
INNER JOIN delivery.dbo.syspublications ON
delivery.dbo.sysextendedarticlesview.pubid =
delivery.dbo.syspublications.pubid
INNER JOIN master..sysservers ss on ss.srvid =
delivery.dbo.syssubscriptions.srvid
left outer JOIN delivery.dbo.sysprotects on
delivery.dbo.sysprotects.id = delivery.dbo.sysobjects.id
left outer JOIN delivery.dbo.sysusers on
delivery.dbo.sysprotects.uid = delivery.dbo.sysusers.uid
where action in (193,195,196,197,224)
and srvname = 'yyy'
order by ss.srvname, delivery.dbo.syspublications.name,
delivery.dbo.sysobjects.name, delivery.dbo.sysusers.name

If you aren't interested in a particular subscriber, then this'll be
simpler:

CREATE PROCEDURE spGetCPDBPermissionsatBT AS
SELECT 'Grant ' +
case
when action = 193 then 'SELECT'
when action = 195 then 'INSERT'
when action = 196 then 'DELETE'
when action = 197 then 'UPDATE'
when action = 224 then 'EXECUTE'
end as Grant1,
' ON [' + delivery.dbo.sysobjects.name + '] TO ' +
delivery.dbo.sysusers.name as Grant2
FROM delivery.dbo.sysextendedarticlesview
INNER JOIN delivery.dbo.sysobjects ON
delivery.dbo.sysextendedarticlesview.objid = delivery.dbo.sysobjects.id
INNER JOIN delivery.dbo.syspublications ON
delivery.dbo.sysextendedarticlesview.pubid =
delivery.dbo.syspublications.pubid
left outer JOIN delivery.dbo.sysprotects on delivery.dbo.sysprotects.id =
delivery.dbo.sysobjects.id
left outer JOIN delivery.dbo.sysusers on delivery.dbo.sysprotects.uid =
delivery.dbo.sysusers.uid
where action in (193,195,196,197,224)
and delivery.dbo.sysusers.name not in ('RO','RW')
order by delivery.dbo.syspublications.name, delivery.dbo.sysobjects.name,
delivery.dbo.sysusers.name

Reply to this Message