[dba-SQLServer] Users in SQL Server

jwcolby jwcolby at colbyconsulting.com
Sun Dec 4 12:50:07 CST 2011


I understand and agree but that really isn't the point in this case.  I have total access and nobody 
else understands anything, never mind the tiny amount that I understand.  My problem really is that 
I don't understand the whole SQL Server users / roles thing and I keep screwing stuff up.

I had SQL Server 2005 express set up and functioning.  I had set up users at the server level to 
allow specific limited functionality to a couple of "users".  These weren't really users as in the 
domain, but rather a DiscopApp user, in other words my disco (Access) application.  That user was 
set up as a user at the server level, without which (AFAICT) the application could not even log on 
to the server.  Then the database(s) had to have that user as well or the application couldn't 
execute stored procedures and stuff.

1) Log on to sql server
2) Do stuff in the database.

But... the users in the server security system are NOT the same user as the same user with the same 
name in the database security system.  New physical server, new installation of SQL Server.  I 
disconnected the database and reconnected it in the new machine.  The database still had the users I 
set up but... those user's aren't the SQL Server security.  When I tried to add the users in SQL 
Server side I could do that but I couldn't add their permissions in the database because... THEY 
ALREADY EXISTED (in the database).

What an abortion.

I go Google this mess and yep... it's an abortion!

This is really one of those places where our full time SQL Server admins are shaking their heads and 
telling me (or at least thinking) that since I don't have the time to learn all of the arcane 
asininities required to be a knowledgable SQL Server admin I have no business using SQL Server. 
Sadly, I have no choice.  Sadly SQL Server makes no apparent effort to provide wizards to help me 
out.  It seems like a no brainer to have a little wizard that would move the user stuff stored in a 
database up to the server level so that the user could be used to log in to SQL Server.

Sadly I have to learn the arcane asininities required to do this from the command prompt. 
Apparently it can be done from the command line (some how).  Or tell my client they need to hire a 
SQL Server admin and put him to work doing office installs in the 95% of his time that will be free 
to do things other than SQL Server.

SQL Server is insanely powerful when it is not beating you over the head with arcane asininities.

;)

John W. Colby
Colby Consulting

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

On 12/4/2011 1:13 PM, Arthur Fuller wrote:
> For over five years I worked (full-time but contract, gradually lessening
> to part-time contract, as more and more got put in place). But pretty much
> at the outset, I told the owners of the firm that I was the super-user, at
> least insofar as the database was concerned, and that I would suggest
> db-inherited permission levels according to SQL Server. This (at the db
> level) meant four levels of permission: sales, department manager, senior
> manager, and God (me). the only one with absolute control over the db. I
> told them flat-out that I didn't trust their modest skills to fork with
> their essential data.
>
> Now, obviously this approach wouldn't have worked in a larger organization,
> but the underlying point remains similar, IMO. Either you trust me, as the
> designer and developer of your core asset (the bricks and mortar, in
> comparison, are trivial -- the Big Asset is the db), to serve and protect
> and revise and re-design as exigencies and business priorities emerge, or
> you don't. And if the latter, then I'm off to work for someone who
> understands and respects boundaries.
>
> To this admittedly authoritarian attitude, I also provided the means,
> passwords, etc. that would be needed in the event of a bus rolling over me,
> or similar unforeseen events.
>
> On the other hand, I spent a while on a multi-million dollar project
> managed (allegedly) by a very large consulting firm, owned in half by MS,
> which dictated the use of MS products entirely throughout the solution.
> Numerous problems that could have been solved almost instantly by switching
> to Oracle or PostGres were off the table due to the heavy participation of
> MS.
>
> The information was very confidential, and the first thing the DB designer
> did was isolate virtually everyone from accessing the actual data. When
> creating a sproc or view, we got back rows of asterisks, so as not to
> violate privacty restrictions. We were held down to verifying accuracy by
> the number of rows returned by any given sproc or view or query. On this
> project, to further complicate matters, there were 8 servers, each with a
> DB of a TB or more, and some of the joins required grabbing data from more
> than one server, and occasionally more than one DB on some of the servers.
>
> It took a couple of years to figure out how all this might work (maybe that
> means I'm insufficiently aware of these complexities; but my superiors had
> the same problems: how to mask all the data while also being able to prove
> that any given sproc delivered the required result-set). I call this
> "working in handcuffs"; I appreciate the need for secrecy and privacy in
> certain domains, and in this particular example can fully appreciate why us
> developers were not granted access to the specific data in the rows
> returned (without giving too much away, think "rows containing HIV-positive
> persons, their health records, their history of physicians, etc.).
>
> All this pretty much describes why I prefer to work for SMBs than giant
> federal or even provincial ministries. I have worked for both, at length,
> and I prefer the smaller and more achievable projects.
>
> Just IME and my $0.02.
>
> A.
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>




More information about the dba-SQLServer mailing list