December 26, 2002 at 12:43 pm
I was wondering if it is possible to replicate users/roles and their permissions from one database to another. I appreciate your replies!
December 27, 2002 at 9:47 am
Not through SQL replication. DTS has a login transfer task that you could use.
There is also a createdate in syslogins that you could use to build something.
Steve Jones
December 27, 2002 at 9:54 am
Yeah I looked into the Login Transfer Task, it doesn't transfer the permissions for me. Which I most definitely need. We have 8 production servers (each with a reporting server) and on each instance 80 databases. We are only replicating a few of the tables to the reporting server.
Right now we are gonna try a method where we copy the user tables (sysusers, sysmembers, syspermissions) over into a holding area and then use that to update the reporting server. One other question, are there other tables I will need?
December 27, 2002 at 12:26 pm
Sysprotects maybe? Getting your login SIDs synced up front will save a lot of hassle, beyond that not bad to write a job to sync the data. Dont know if will do system tables, but RedGate has a data compare tool that will generate the SQL to bring two tables in sync.
Andy
December 27, 2002 at 6:25 pm
Yeah we looked at some software solutions out there, and none of them really had what we needed to do. I'm currently using sql-dmo scripting to do a lot of the work. The main problem is that the client has 8 servers each with their own reporting server. There are at least 80 databases on each server, with differernt users, etc. I also need to make it work for when they add databases, with little or no work on their side. So far I think I have a solution that will work for now. We're creating an additional database on each server to house the sysusers, syspermissions, and sysmembers tables for each database ( i think those are the 3 i need) We then replicate this DB onto the reporting server and update each database from there. I'm pretty much halfway through it (the parts on the production side) here's the vbs im using so far, let me know if you see some great problems. I am still working on it so, bear with me on some issues. I still need to figure out how to add any new tables (tables that get created by the script in DTS_Store) to the Replication area for that publication, hoping to figure out how to do this with sql dmo, perhaps in the same script.
This will basically be it, I am going to add the parts to populate the tables, etc.
Here'sthe script:
http://www.sullivan-street.com/script.txt
The main problem for me has been that this is mostly way beyond anything I have done in SQL. I really appreciate your criticisms. Hope you all had a good holiday!
December 28, 2002 at 6:17 am
Cant think of a better way so far. If I can restate the problem - you've got 8 servers that each replicates to a reporting server, on any of those servers someone may add a user, change permissions, etc, and you need to make those changes match on the reporting server - is that right?
Andy
December 28, 2002 at 9:20 am
Yeah, the users will be added or changed on the first 8, and will need to be replicated to the reporting servers.
December 28, 2002 at 10:10 am
Interesting task. It's pretty low intensity (how many changes can their be?), not sure it's worth consolidating and replicating. I think I'd approach this initially from the one publisher (db) possibly many subscriber scenario, once that works you just need a config table that lists all the publishers, iterate through those. Not much different than your proposal.
A different approach would be to only allow (by convention) changes to be made via a custom app, then as you applied user/role/permission changes you would apply to both the publisher and all subscribers at the same time, so they would never be out of sync.
I just have one publishing server with about 250 db's, replicating all to a single reporting server. My permissions seldom change so I just apply a post snapshot script to set up the roles.
Finally, for reporting are your permission requirements that great? Most likely you treat the data as read only, might make more sense to set up a single role (or use db_datareader).
Andy
December 28, 2002 at 10:52 am
Yeah that was my initial thought, but the client didnt like the idea so much, and as I need to get this done today, well whatever i can do for now will be the initial solution. There might be time in the future to change it to something more robust.
Oh, I thought of another thing that might throw me off. With the passwords etc, is there a special way i should move these?
Also, do you know of any good examples for adding a mergearticle to a push publication with dmo?
By the way thanks for all the help.
December 28, 2002 at 5:41 pm
Passwords are stored as varbinary in sysxlogins in master, no big deal to either move, or you can use sp_addlogin, it has the option to move a password already encrypted. Once you add the login then you have to fix the SID to match the one on the publisher.
No example handy on the mergearticle, but I can work on it if you still need it...say on Monday. Got a few things to get out of the way before then.
Andy
December 30, 2002 at 5:14 pm
Yeah that's what I figured/hoped. So far I have it mostly done. I am successfully replicationg users to the reporting servers. I ran into a transaction log issue though, I think I have that figured out as well. For now its working fine to make sure new users get replicated etc, I just need to add something that updates any changes to the user. So like when they change the passwords, I'll need to update it. I'm hoping that this is just as easy as moving them around is. I don't think that shoould be too hard.
The only thing I'm really looking forward to is getting it to add any newer tables to my replication. I been looking high and low online and as yet haven't seen it anywhere. Looking at msnd sql dmo refrence was a good start, this is an Add method for MergeArticle, Publicaitons, etc but of course the "instructions" on how to do it read like japanese vcr instructions and they had no handy example like they do on tons of other sql dmo object ref. pages. If i can get that piece in I will have something that the client with love. As they add customers (databases) to the servers they won't need to go in a change the replication.
Anyway my head hurts too much to keep going on with this today. I think i'll take a break.
December 30, 2002 at 5:15 pm
oh btw...this is what i have come up with so far on the merge article thing...of course this is like the 50th try...error is thrown on the .Add line.
'add to replications
set rPub = oSrv.Replication.ReplicationDatabases("DTS_Store").MergePublications("DTS_Store")
set mArt = rPub.MergeArticles
set newArt = mArt.MergeArticle
newArt.Name = "Test"
newArt.SourceObjectName = "Test"
newArt.SourceObjectOwner = "dbo"
mArt.Add(newArt)
December 30, 2002 at 6:21 pm
Cool, I'll try to work on it tomorrow morning. Maybe when you're done we can talk you into writing up the experience as a case study and submitting along with some code?
Andy
December 30, 2002 at 7:09 pm
You're not far off. Tested this, works ok:
Dim oserver As SQLDMO.SQLServer
Dim oDB As SQLDMO.ReplicationDatabase
Dim oPub As SQLDMO.MergePublication
Dim oArticle As SQLDMO.MergeArticle
'create standard server object first
Set oserver = New SQLDMO.SQLServer
With oserver
.LoginSecure = True
.Connect "yourserver"
End With
'get this to be a little closer to where we will do the work
Set oDB = oserver.Replication.ReplicationDatabases("MERGE_SOURCE")
'pub normally has same name as db, doesn't have to though
Set oPub = oDB.MergePublications("MERGE_SOURCE")
'create the article
Set oArticle = New MergeArticle
With oArticle
.Name = "SecondArticle"
.SourceObjectName = "SecondArticle"
.SourceObjectOwner = "dbo"
End With
'add it to the collection
oPub.MergeArticles.Add oArticle
'clean up
Set oArticle = Nothing
Set oPub = Nothing
Set oDB = Nothing
oserver.DisConnect
Set oserver = Nothing
I think there may be more work involved if you're adding an article to a publication that has subscribers, have to generate a snapshot I think. Transactional won't let you do this without dropping the subscriber, merge is better in that regard.
Andy
January 3, 2003 at 5:24 pm
OMG you are awesome, I was getting close, thanks for the help. I'll be going back into the client's place on Monday to add this part, i'll post the changes i make for the snapshot etc.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply