michael at ddisolutions.com.au
Sun Dec 4 23:55:26 CST 2011
Hi JC, I think this is the sproc you need. sp_change_users_login You'll need to look it up in help for usage details. You can use it to fix orphaned logins with the same name. Which is what you get when you restore a db on another system. The names might be the same but the ID's underlying the names will be different. This sp fixes that issue. *Tip - you can save useful sprocs as a template and reuse them when needed. There should be a tab on the right hand side of SSM Studio. If this doesn't solve your problem then I have misunderstood! Cheers Michael M >I just discovered that if I delete the user out in that database and then set the rights through the user back in the server security stuff it happily accepts my changes and re-adds the user to the database. Unfortunately after I did this delete / re-add rigamarole I discovered that all of the rights to objects in the database were removed. For example I had rights to execute stored procedures assigned to these users but after deleting the users the rights to execute were lost. Sigh. This SQL Server Users stuff is really developer unfriendly. John W. Colby Colby Consulting Reality is what refuses to go away when you do not believe in it On 12/4/2011 10:59 AM, jwcolby wrote: > > Several times now I have run into an issue where SQL Server does not > have a pair of users I use for my Access application called DiscoAdmin > and DiscoApp. The problem is that one of the the databases that I am > picking up on that server was already set up and has those users. When I try to set those users up in the server's security it tells me that "the server principal 'DiscoApp' already exists. > Basically I am able to create the user but not assign rights - db_reader, db_writer etc. > > I have always been fuzzy about how this stuff works and I just need an > explanation of why it won't allow me to set these rights even though > the user exists and the check boxes are enabled when I select that > database. I just discovered that if I delete the user out in that > database and then set the rights through the user back in the server > security stuff it happily accepts my changes and re-adds the user to > the database. This just seems strange. If I don't do this rigamarole then I have a DiscoApp in the database with a set of rights for that database but when I look at it back at the server level it does not reflect those rights for that user for that database. > > Any assistance great fully accepted.
More information about the dba-SQLServer mailing list