July 19, 2005 at 6:25 pm
When I add a new article to a Transactional Replication system (sp_addsubscription), everything happens okay and the replicated table shows up at the subscriber, no problem. However, there are no permissions on the new table in the subscriber database so noone (other than admins) can see it.
How do I set permissions by default on new subscriptions when they are added?
Yes, I can log into the subscriber and do it by hand, but this step is embedded in a nightly job. I need have the permissions set as soon as the new article is transferred as the table is needed before I could possibly get out of bed in the morning. 😉
Any ideas?
July 20, 2005 at 5:49 pm
July 21, 2005 at 3:17 pm
How would you recommend using a reference table of users and permissions to grant permissions automatically? Use a job timed to go off after the table should be reloaded and before it's needed?? What I'm trying to do is have the replication process set the permissions since that's the only thing that knows when it's hashed over the target table.
Thanks!
Steve G.
July 22, 2005 at 7:55 am
You could pass in the values of the tables as variables in a stored procedure and loop through & grant permissions. You could add the SP as the last step in the replication job so that it always happens immediately after replication completes.
Our users don't have permission problems with our replicated tables. Hmmm.
We're replicating to a data warehouse-like environment, in which users can run queries & pivot tables. I add the users to a global group through Active Directory, then add the global group to a local group on the server, then give the local group "read" permissions to the database. Any new tables can be read by users with database read permission.
July 22, 2005 at 5:34 pm
Hrrm... I have a stored proc that I can run to grant all necessary permissions. What I'm seeing is that when a subscription is added a new table is created with default permissions of no access by no body, no how. 😉 Pretty typical. I'd like to somehow default it so that new tables would have "select" access to my users. Even select access by "public" would be adequate.
Oh, and I forgot to mention that this is a "push" subscription.
Steve G.
July 25, 2005 at 9:25 am
Object permissions within the datatabase are not replicated by default.
This is because replication usually involves replicating data and schema to other servers, where logins may not be defined or defined differently.
The permissions are 'lost' after applying a snaphsot.
In SQL7.0 you will have to apply them manually. In SQL 2000, there is an option within the replication configuration when applying a snapshot to apply a script before and/or after the snapshot. Define the appropriate script here, and replication will take care of the rest.
It is found in the Publication article properties.
Paul R Williams.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply