[dba-SQLServer] Users in SQL Server

Yea, this stuff is a mess like I've seldom seen.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 12/4/2011 11:47 AM, Dan Waters wrote:
> I was working with a customer and needed to get a database copy for myself.
> For some reason backup wasn't working (an IT issue), so I decided to detach
> the database, copy it, and then reattach it.  However, I couldn't reattach
> it because all my rights were associated with that database - once the
> database was gone so was my right to attach a database!  Gotcha!
>
> Dan
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Sunday, December 04, 2011 10:14 AM
> To: Discussion concerning MS SQL Server; Access Developers discussion and
> problem solving
> Subject: Re: [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.
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>