[AccessD] [dba-SQLServer] Users in SQL Server

John - as you are discovering (the hard way I might add),
SQL Security is a whole specialty onto itself....in fact, there have been
books written about just that.
Here's one that may be helpful:
http://www.amazon.com/Microsoft-Server-Administrators-Pocket-Consultant/dp/0
73562738X/ref=sr_1_4?s=books&ie=UTF8&qid=1323019853&sr=1-4
and here's another:
http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067
233044X/ref=pd_sim_b_6


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-
> bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Sunday, December 04, 2011 11:14 AM
> To: Discussion concerning MS SQL Server; Access Developers discussion
> and problem solving
> Subject: Re: [AccessD] [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.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com