Actually, this is not completely possible.
There are two authentication modes for SQL Server (2008R2):
- Windows Authentication: stronger security, Kerberos protocol.
- Mixed Mode (Windows and SQL Server): use of “sa” login. Less secure. Best option for web applications.
I’ve got a web server (IIS) andwhen I log into Windows (2008R2), if I open SQL Server, Windows Authentication is selected by default and it lets me access without any password.
This is because, by default, the Windows administrators group is assigned the “sysadmin” server role. We can remove them from this server role or remove completely the group from the SQL Server logins.
So, I did the following:
- Access SQL Server as “sa” user.
- Go to Security/Logins
- Right click on the BUILTIN\Administrators login or PC-NAME\USER-NAME login.
- Choose Properties
- Go to Server Roles
- Uncheck “sysadmin” role
- Make sure it has only the “public” role selected.
- Click OK
This way, Windows administrator can access SQL Server but without permissions to modify anything.
However, “sa” user can still access SQL Server with full privileges.
Of course, it’s not recommended to use “sa” login for any application (ASP.Net).
You better create a specific login for each application, with just the needed permissions to access its database.