Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

object permissions with replication

Posted on 2011-10-06
18
Medium Priority
?
254 Views
Last Modified: 2012-05-12
in a publication-subscription situation, if you put object permissions on source, does that also get carried over?
0
Comment
Question by:25112
  • 9
  • 8
18 Comments
 
LVL 5

Author Comment

by:25112
ID: 36924222
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
 
LVL 15

Accepted Solution

by:
Faiga Diegel earned 668 total points
ID: 36925488
I guess no. Replication only focuses on data level or object level. It replicates data, not permission.
0
 
LVL 5

Author Comment

by:25112
ID: 36925905
ok - thanks.

if you can find it on msdn as an article, could you share..
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36926089
It depends.  Did you request that the article copy permissions?
0
 
LVL 5

Author Comment

by:25112
ID: 36926419
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1332 total points
ID: 36927129
>>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
 
LVL 5

Author Comment

by:25112
ID: 36927748
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1332 total points
ID: 36928060
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36987561
Here is the link to the sp_addarticle information in SQL Server BOL:
http://msdn.microsoft.com/en-us/library/ms173857.aspx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36987604
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
 
LVL 5

Author Comment

by:25112
ID: 36990016
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36990047
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
 
LVL 5

Author Comment

by:25112
ID: 36992427
OK.

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

Expert Comment

by:Anthony Perkins
ID: 36993982
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
 
LVL 5

Author Comment

by:25112
ID: 36995141
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36996961
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36996968
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
 
LVL 5

Author Comment

by:25112
ID: 37001917
makes good sense .. thanks a lot for your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question