[dba-SQLServer] Users in SQL Server

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