[dba-SQLServer] Users in SQL Server

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.