object permissions with replication

in a publication-subscription situation, if you put object permissions on source, does that also get carried over?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

25112Author Commented:
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)
0
Faiga DiegelSr Database EngineerCommented:
I guess no. Replication only focuses on data level or object level. It replicates data, not permission.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
ok - thanks.

if you can find it on msdn as an article, could you share..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
It depends.  Did you request that the article copy permissions?
0
25112Author Commented:
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
0
Anthony PerkinsCommented:
>>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.<<
I suspect we are talking at cross-purposes. By article I meant any database object (including tables) that you have added to a publication.  I am trying to explain to you that you can have replication copy permissions.

Hopefully this is clear this time.
0
25112Author Commented:
OK thanks.. great that is what I am asking about.. could you get a msdn page that explains how to, and details..

thanks again ...
0
Anthony PerkinsCommented:
It is all covered in SQL Server BOL for sp_addarticle.  You have to make sure the @schema_option has a value that you can do a bitwise OR with 0x40000000.
0
Anthony PerkinsCommented:
Here is the link to the sp_addarticle information in SQL Server BOL:
http://msdn.microsoft.com/en-us/library/ms173857.aspx
0
Anthony PerkinsCommented:
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.
0
25112Author Commented:
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.
0
Anthony PerkinsCommented:
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.
0
25112Author Commented:
OK.

did you mean this screen to add the 'replicate property' ?
replicate.bmp
0
Anthony PerkinsCommented:
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.
0
25112Author Commented:
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'

Open in new window

0
Anthony PerkinsCommented:
>>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.'

Open in new window

0
Anthony PerkinsCommented:
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.'
0
25112Author Commented:
makes good sense .. thanks a lot for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.