Authentication to SQL Server

[Mark]

I have an adp front end (Access 2002) connected to SQL Server (2000). I am
creating various views and SP’s in SQL Server. One manner of authentication
to SQL Server is through my windows logon. The other is with SQL Server
itself. My question is, are there any advantages to either means? I have read
that possibly issues with views exist depending on this authentication,
(using a windows account). Also, I’m concerned with the ownership of objects
created. Setting up access to SQL Server is a bit of a pain in this shop and
I want to get it right.
Thanks for your insight.
Mark

 

[Paul Shapiro]

If all your users are internal, and have Windows logons in your domain, then
integrated authentication is both easier and more secure. Microsoft suggests
not enabling SQL Server authentication unless you need it, generally because
you have users without domain accounts. I've never heard of issues with
views when using integrated Windows authentication.

If you use integrated authentication, then read up on assigning permissions.
The recommended approach is to define security groups for each user role. A
role could be things like data-entry clerk, marketing, sales, supervisor,
administrator, etc. Add the appropriate user accounts to each security
group. Assign permissions on SQL Server objects to the security groups, not
to individual users.

If your organization already has people responsible for securing SQL Server,
talk to them before you go any further. They should be a helpful resource,
and if they're also in control then you need to be working with them anyway.