[dba-SQLServer] Users in SQL Server

 >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.