[dba-SQLServer] Users in SQL Server

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.