[AccessD] SQL Server security

David McAfee davidmcafee at gmail.com
Wed Jan 5 13:00:56 CST 2011


One reason that I use SSA over Windows Authentication is there are
departments here
that have high turn over and or several factory workers that "share" a
common login.

Our sys admins don't like to create Win logons for new people for some
reason. (Laziness? idk)

I, on the other hand, am a date stamp / user stamping whore.

As mentioned before, just about everything done in my systems is done via
insertion, so I
time stamp every record and stamp the userID as well.

I create individual SQL logins, since I maintain that end of things and
simply add the people
to their required roles. Roles are given rights to stored procedures so it
isn't very hard to do.


Another time I use SSA is when dealing with users that only give us data via
web services,
obviously they aren't local and only have/need access to the SQL Server.

D

On Wed, Jan 5, 2011 at 10:40 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> Thanks Charlotte.  I want to use Windows authentication if I can.
>
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 1/5/2011 1:18 PM, Charlotte Foust wrote:
>
>> Windows Authentication should work, John.  That's what we did for our
>> clients at my last employer's.  You can certainly create specific
>> users and groups and roles on the server.  We handled most of the
>> specifics in code (.Net, natch) but we had only two groups for users,
>> readonly and readwrite, the latter of which included deletes, but our
>> code limited that capability.
>>
>> Charlotte Foust
>>
>> On Wed, Jan 5, 2011 at 10:00 AM, jwcolby<jwcolby at colbyconsulting.com>
>>  wrote:
>>
>>> I am having performance issues in a largish Access application, a
>>> Disability
>>> Insurance Claim call center app.
>>>
>>> I have one particular table which is not huge in terms of field count but
>>> it
>>> does have a lot of records and most of the fields are indexed, and it has
>>> about 800K records in it.  This table holds "contact" info, as in phone
>>> calls that the users have.  They document every "contact" with every one,
>>> claimants, doctors, lawyers, etc. into a memo field and also date of
>>> call,
>>> ClaimID FK, employee id FK, contact type id FK etc.  Kind of a mini
>>> center
>>> of the universe for this application.
>>>
>>> The result is that people are storing new records in this table
>>> constantly
>>> throughout the day and we are getting a lot of "record locked..." issues
>>> caused by (AFAICT) the time it takes Jet to store the records and update
>>> all
>>> of the indexes, and probably the memo storage area of the mdb.
>>>
>>> Just to give a picture, this one table has been moved out to it's own mdb
>>> and that mdb is about 700 megabytes after a compact.  Most of the rest of
>>> the database (150 tables) is in another mdb and after compact that
>>> database
>>> is 800 megabytes, so this one table is close to as big as the rest of the
>>> db.
>>>
>>> I do not have experience in a transactional database using SQL Server,
>>> but I
>>> am thinking that SQL Server express 2005 will not have an issue keeping
>>> up
>>> with this kind of usage - 25 users adding records to this table all day
>>> without causing locking issues like I am seeing now.
>>>
>>> My issue at this point is that they use a network logon and force the
>>> users
>>> to change their password every 30 days.  Is SQL Server going to use that
>>> same network username / password database or does it use a list of
>>> usernames
>>> / passwords physically on the server itself?  IOW will Windows
>>> authentication work or will I need to go to SQL Server username /
>>> password?
>>>
>>> --
>>> John W. Colby
>>> www.ColbyConsulting.com
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>
>>>
>>  --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list