Security - Lock Database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg - 27 Jan 2008 05:16 GMT

I want to make some changes regarding accessing my SQL Server Database, but
before I start playing around with it, I want to post something to see if
anyone can give me some guidence so I don't end up locking myself out of my
SQL Server Database or SQL Server all together.

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?

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.

Ultimately, what I am looking to achieve. I want my customers to have full
access to their SQL Servers, but I don't want anyone to be able to browse
the structure of my database, if not authorized. Not that my database
structure would give away much, I'd just like to protect it from prying
eyes.

Thanks.

Reply to this Message

Greg - 27 Jan 2008 05:19 GMT

Just want to add one more thing to this.

If I can't stop someone from looking at the database structure, is it
possible to keep someone from looking at the data? (From within SQL Server)?

Thanks.

Greg

> I want to make some changes regarding accessing my SQL Server Database, but
> before I start playing around with it, I want to post something to see if
[quoted text clipped - 20 lines]
>
> Thanks.

Reply to this Message

Uri Dimant - 27 Jan 2008 08:56 GMT

Greg

> 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?

Create a SQL Login and set up SQL Server with Mixed Authentication.

> 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.

Not possible, sysadmin has to everything access

>If I can't stop someone from looking at the database structure, is it
>possible to keep someone from looking at the data? (From within SQL
>Server)?

This someone is not a SA, right?

• Create a new SQL login "login1"
• Create a user named “login1” in master database
• Grant CREATE DATABASE to login1
• While impersonating login1, create a database called “dbteste”
• Revoke CREATE DATABASE permission from login1
• Revoke VIEW ANY DATABASE permission from PUBLIC
• Register this server as login1
• From the “login1” session, expand database tree. Now, you should see
master, tempdb, dbteste
• Grant VIEW ANY DATABASE to PUBLIC
• From the “login1” session, you should see all the databases

This works perfectly if the user is the owner of the database

>I want to make some changes regarding accessing my SQL Server Database, but
>before I start playing around with it, I want to post something to see if
[quoted text clipped - 20 lines]
>
> Thanks.

Reply to this Message

Ekrem Önsoy - 27 Jan 2008 10:30 GMT

How about using a dedicated SQL Server Instance (not another server, just
another instance on the same SQL Server server) for your database?

Signature

Ekrem Önsoy

>I want to make some changes regarding accessing my SQL Server Database, but
>before I start playing around with it, I want to post something to see if
[quoted text clipped - 20 lines]
>
> Thanks.

Reply to this Message

Erland Sommarskog - 27 Jan 2008 11:01 GMT

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.

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.)

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Reply to this Message

Greg - 27 Jan 2008 16:10 GMT

My apologies for not specifying what version of SQL Server I'm using. I'm
using SQL Server 2005.

> > 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
[quoted text clipped - 20 lines]
> saying which SQL Server version you are on, is difficult, as there are
> big differences between SQL 2000 and SQL 2005.)

Reply to this Message

Erland Sommarskog - 27 Jan 2008 23:34 GMT

> My apologies for not specifying what version of SQL Server I'm using. I'm
> using SQL Server 2005.

OK. If you go the road that I suggested, by making everyone db_owner in
all databases, but yours, you will need to grant them a couple of
server permissions. You find the list on
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7e880a5a-3bdc-491f-a167-
7a9ed338be7f.htm

Some of these will give them access to your database, for instance
CONTROL SERVER, so stay away from that one. Others like VIEW SERVER
STATE should be safe - I think.

I would suggest that you read more about these permissions yourself,
and also test which fulfils your criteria.

Signature

Reply to this Message