Too often developers and administrators alike find an easy solution to providing data access is to give users more permissions than are truly required. In the development environments I’ve worked in, developers always worked with full local and server admin rights and full database rights. This usually resulted in deployment issues with the expected response of “well, it worked fine for me.”
Your key to data access
Whenever possible now, I work under the principle of least privilege, giving users the minimum amount of permissions needed to complete their tasks. This extends right to the service account that runs SQL Server – it’s a Domain User account with no elevated rights on the server beyond what the SQL Server installation process conveys.
My first task after completing a new SQL Server 2000 installation would always be to remove the BUILTIN\Administrators group in SQL that gave anyone with membership in the local Administrators group full access to the SQL Server. Why in the world was it assumed that server and server application administrators were the same people? I was so pleased to not see that group in SQL Server 2008.
I found application roles in SQL Server 2000 to be frustrating. I liked the concept of granting permissions to a single role and then have users access the application through the role, but I lost all ability to keep an audit trail. The next best option was groups through Windows Authentication, but it enabled anyone with access needed for an application to also get to the database through other means.
The improvement to SQL Server is loginless users. Users authenticate to the server instance with their own credentials, but by accessing the database, the user then impersonates the loginless user for permissions via EXECUTE AS. The server can still audit activity because the user authenticated with their own credentials, but do not have direct access to the database with those credentials.
To create a loginless user:
CREATE USER AppUser WITHOUT LOGIN GO
To grant a group or user permission to impersonate the loginless user:
GRANT IMPERSONATE ON USER::AppUser TO [MyDomain\LeeAnne]; GO
To impersonate the loginless user and gain all permissions granted to it:
EXECUTE AS USER = 'AppUser' GO
This feature will be invaluable with new development. It is my hope when I eventually migrate databases from SQL Server 2000 to 2008 that I can find someway of implementing loginless users for current Microsoft Access-based applications that currently use linked tables for database access.
