object permissions with replication

for example, sql 2000 database tables are the sources, and the subscriptions are received by a sql 2005 database. let's say usera needs select permissions on 10 tables.

i am able to set it in the destination.. but if i set it in source, will be gets trasmitted?

(this way, if the same user needs that permission in several servers, where the database is being replicated, it will be easy and efficient)

ok - thanks.

if you can find it on msdn as an article, could you share..

It depends.  Did you request that the article copy permissions?

when i meant by article, i was requesting if you are able to find a msdn link confirming that permissions won't carry through in the replication.

in the programmer's meeting, we just need to bullet that point with a link ,if available.. that is why

OK thanks.. great that is what I am asking about.. could you get a msdn page that explains how to, and details..

thanks again ...

Here is the link to the sp_addarticle information in SQL Server BOL:
http://msdn.microsoft.com/en-us/library/ms173857.aspx

You can find a full explanation under @schema_option.  Look for "Replicate permissions".

You can list all the information for any article by using sp_helparticle.

acperkins, my fault: i did not clearly say the publication database is sql 2000. (i only hinted it in 36924222) so I am afraid you will have to guide me from http://msdn.microsoft.com/en-us/library/aa239392%28v=sql.80%29.aspx rather.

I have no idea if that was supported back then with SQL Server 2000.  The fact that it is not mentioned, makes me suspect that it is not.  Why not add an article with the Replicate Permissions flag set, then query it to confirm it was accepted, and finally drop the article.

If that is not acceptable, I suggest you open a new question relating back to this one and clearly indicating that the publisher is SQL Server 2000 in the original question (I missed it in your new thread).  Also, don't include SQL Server 2005 zone as it is misleading.

OK.

did you mean this screen to add the 'replicate property' ?
replicate.bmp

I was referring to:
1. Adding an article using sp_addarticle.  If no error then
2. Using sp_helparticle to verify the results.
3. Drop the article using sp_droparticle.

OK, i tried the below code

after that, now,
sp_helparticle 'PC_to_BOSS1' shows me the new article with schemaoption is 0x0000000040000000

does that mean even in sql 2000, if i add a permission that user abc can select this table, will it get carried over to all the suscribers? (the refresh will happen once a week, so i can't test right away)

if yes, why does not the sql 2000 bol does not talk about it (0x40000000)?

also 0x40000000 becomes  0x0000000040000000: is that OK?

thanks acperkins.

exec sp_addarticle 
@publication = N'PC_to_BOSS1', 
@article = N'tblStatusCode', 
@source_owner = N'dbo', 
@source_object = N'tblStatusCode', 
@type = N'logbased', 
@description = N'', 
@creation_script = N'', 
@pre_creation_cmd = N'drop', 
@schema_option = 0x40000000, 
@auto_identity_range = N'false', 
@destination_table = N'tblStatusCode', 
@status = 16,
@vertical_partition = N'false', 
@ins_cmd = N'CALL sp_MSins_tblStatusCode', 
@del_cmd = N'CALL sp_MSdel_tblStatusCode', 
@upd_cmd = N'MCALL sp_MSupd_tblStatusCode'

Select allOpen in new window

>>does that mean even in sql 2000, if i add a permission that user abc can select this table, will it get carried over to all the suscribers? <<
I suspect so.  You will have to verify.

>>if yes, why does not the sql 2000 bol does not talk about it (0x40000000)?<<
I have no idea.  Perhaps it was undocumented for whatever reason.

>>also 0x40000000 becomes  0x0000000040000000: is that OK?<<
Yes and no. Strictly speaking 0x40000000 is not the same as 0x0000000040000000.  However, what you are overlooking is that @schemaOption is a bitwise value of all the attributes and so:
A. It is getting set correctly.
B. By setting this single value you are negating all the other attributes.  Whether that is appropriate or not for your test, I will leave up to you.

This code should illustrate what I am saying:

DECLARE @schema_option binary(8)

SET @schema_option =  0x40000000 

IF @schema_option =  CAST(0x0000000040000000 AS binary(8))
	SELECT 'They are equal'
ELSE
	SELECT 'They are not equal'
	
IF CAST(@schema_option AS integer) & CAST(0x40000000 AS integer) > 0
	SELECT 'Replicate permissions bit is set.'
ELSE
	SELECT 'Replicate permissions bit is set.'

Select allOpen in new window

And that last test should have obviously been:
IF CAST(0x0000000040000000 AS integer) & CAST(0x40000000 AS integer) > 0
      SELECT 'Replicate permissions bit is set.'
ELSE
      SELECT 'Replicate permissions bit is not set.'

makes good sense .. thanks a lot for your help.