Re: Security - Lock Database



Greg (gjsetnes@xxxxxxxxxxx) writes:
At my office, you are required to use SQL Server Authentication to login
to the SQL Server, which is what I want to achieve at a minimum. What
steps should I take to lock down my SQL Server so that it requires an
SQL User Account and Password to login?

There is no way do disable Windows authentication (unless you are still
on SQL 6.5) as such. But you can of course avoid granting server access
to Windows accounts, and also deprive BUILTIN\Administrators of access.

Also, I've asked this in a previous post I think, but its not longer
available. On top of locking down SQL Server as a whole, I'd also like to
lock down my specific database as well. Is it possible to provide an SQ
(System Administrator) type user account that has access to everything,
except a specific database? From what I recalled in the past, it was not
possible to restrict an SA type user from being able to delete a database.

Once a user is sysadmin, he is sysadmin everywhere. Your best bet is
to make the user db_owner in all databases, except your secret databaes.

This means that the user would also be locked out from some server-
level permissions. This can be mitigated with various server permissions
if you are on SQL 2005. (Overall, asking security questions without
saying which SQL Server version you are on, is difficult, as there are
big differences between SQL 2000 and SQL 2005.)


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.