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
Books Online for SQL Server 2000 at

Relevant Pages

  • Idiomatic Expressions to Operating System Architecture: Transforming Rigids
    ... <Subject: Re: Idiomatic Expressions to Operating ... <endpoint during a single session * DATABASE + by ... <statements is a new feature of SQL Server 2005. ...
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
  • Re: Error 21002: [ SQL-DMO] User "xxxxx" already exists.
    ... What version and service pack of SQL Server are you running? ... No restoration of database was performed. ... I do not see that user account as orphaned. ... > saved with necessary permissions for the selected database. ...
  • Re: Word 2003/Access2000/SQLSVR
    ... SQL server being where the data is held and this is accessed through a MS ... entire database and that may be say a record for Berkshire. ... "Peter Jamieson" wrote: ... replaced with the first record on the table in use. ...