How can we protect a database in MS SQL 2005 and 2008

Is there a way to protect a specific database within a Microsoft SQL instance in version 2005 and 2008?

We are trying to do this in 2 ways:


An instance of SQL may have 5 databases and we want one of them not to be accessible by the SQL Studio or any other apps unless some sort of password is entered.


If a DB is detached and attached to another instance; we want the other instance being access by its SQL Studio or its apps have access only if a password is used.
Is there a way to go about this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Encrypting the DB or portions of it. SQL level or application level.
Controlling who has rights/access.
If anyone has rights to detach a database, they likely also have backup rights......
Are you looking for

Are you looking for a two factor authentication/authorization into the database?

beside what arnold said: You secure the access to your database with two different methods: SQL Server logins or Windows authentication method. In case of the first a user/password must be created on SQL Server level and this must be assigned to the desired database. In case of the second a Windows user or group of a domain must also be added on SQL Server level and then assigned to the desired database.

Both would be added to the "public" role in this database, then you need to add them once to the desired existing role (or define an own role first and then assign that).

After this procedure the SSMS user can see any database in the instance but can only access the database(s) where his login on SQL Server level was added to and can work only with the objects he has rights to depending on the assigned role (or direct assigned rights).

So it is not needed to enter a password each time (except to connect to SQL Server either with Windows authentication (no password) or SQL Server login (with password)). The user which works with the database independent from the used frontend (like SSMS) cannot do anything more than what was defined in the right system.

If you detach a database and attach it to another SQL Server you may need to create the same users (both kind of logins) on this server first, otherwise only the system admin can work with this database. It may be needed to reassign the users to the attached database as for example the SQL Server logins are saved with different internal IDs although the login name may be the same. In case of Windows authentication the internal ID comes from the Windows domain so it remains the same on every server.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
An instance of SQL may have 5 databases and we want one of them not to be accessible by the SQL Studio or any other apps unless some sort of password is entered.
You can create a login trigger to accomplish this. Example:
CREATE TRIGGER trg_app_check
    IF EXISTS (SELECT 1 FROM sys.dm_exec_sessions
        WHERE program_name <> 'MyProgramNameHere'
            AND LOGIN_NAME IN ('Login1', 'Login2', ..., 'LoginN'))

Open in new window

If a DB is detached and attached to another instance; we want the other instance being access by its SQL Studio or its apps have access only if a password is used.
You need that new instance to have the same logins created. But best solution should be encrypt the database so no one can use it without the master key.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

rayluvsAuthor Commented:

What we are looking for is more control access.


You are correct! Totally forgot we can do that in MSSQL; SQL Server logins or Windows authentication.

Ok, #1 is answered.

As for #2, detach/attach database, what we are looking for if we can incorporate some sort of security to the DB that when detached and attached to another unauthorized sql instance, it won't permit it to be accessible.

Note: re-reading the initial question, we meant: "If a DB is detached and attached to a unauthorized SQL instance or PC; we want that instance or it's PC apps not to be able to access the data"

Vitor Montalvão:

Same thing, we are looking for if we can incorporate some sort of security to the DB that when detached and attached to another unauthorized sql instance, it won't permit it to be accessible.

Can #2 be possible?
Detach/attach or backup require the user to also have access rights to the system's storage area.

Ssms can be used to access the system remotely.

No matter how much you want, if a person has access to the DB, the data can be transferred.

You are seemingly enquiring about how to restrickt access to a user who now has legs say unlimited access.

You can have two factor auth into the server/workstation (windows OS), as well as into a web site ...

The SSMS/SQL server does not see to have that option.
rayluvsAuthor Commented:
So in conclusion, there is no way protect a detached DB from being attached in an unauthorized PC with SQL?
Not based on the mdb/ldb SQL side.  Without encryption that a certificate will be required to access/decrypt the data there is nothing that I am aware of that would require anything..
rayluvsAuthor Commented:
So you are saying if we set the encryption provided by SQL Server 2005/2008 on a database then the data is protected (even though its been attached to an unauthorized PC/SQL instance)?
The encrypted data by SQL will not be displayed unless/until the same user who detached/backed up the data also copies/exports the certificate from the server and U.S. It on the other.....
rayluvsAuthor Commented:
Perfect!  Then we can consider using SQL's encryption for the detach/attach issue.

Last question based on this part, can we encrypt a current database with data or encryption only works at creation time?
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can encrypt/decrypt everytime you want. Just go to the database properties and you'll find the correct option to do that.
My advice would be before you go the encryption route of anpy production db, make sure to test your backup/recovery procedures.
Nothing is worse when a failure/issue cones up that one discovers that while the restore of the db from backup went well, the certificate was not backed up rendering the encrypted data inaccessible.

You need to account for the encryption on the application.

Simpler/less impact could be to use application level/based encryption of specific columns/data. This method means that access to the encrypted data portion can only be done from the application.
Here is MS's
Note it adds overhead to the db server.
rayluvsAuthor Commented:
Vitor Montalvão:

Understood.  Thanx or confirming we can encrypt current working production DB.


Gooud point!  We didn't consider this point. Please tell us if we understood correctly by your comment "You need to account for the encryption on the application": that is, if we proceed encrypting our SQL productions DB, we have to also evaluate apps that are currently working with the DB?  (we have VBA in Excel, Word & Outlook that access theses DB and also other software packages that their DB is in this SQL instance).

If our assumption is correct, we can review the VBAs since we coded it, but what considerations should we take to software packaged currently in use?
Posted the document on enabling the encryption on the DB level.  To avoid the data being transmitted in clear/plain, the document includes enabling the SSL connection.  If SSL connection is enabled and it is currently not, checking whether your Apps include handling of the connection encrypted/unencrypted.
Depending on which mode of encryption you choose, entire DB or you want only certain columns, in certain tables encrypted on the sql level, the data type has to be changed which also has an adverse impact on queries. individually encrypted columns, can not be index thus used in a where clause.
A person who has sufficient access to detach/attach a database will likely have access to export the private/private keys and certificates which are needed to decrypt the data when attached on a different instance, the person could also have the requisite rights to decrypt the DB prior to .......

The other consideration is whether you would incorporate the encryption functionality on the application level. The contents of columns encrypted can be used in the where clause since the where clause would need to include the encrypted version of the data.  difficulty is that your code will vary the input to encrypt, and thus will make it difficult to ...........

Most counter measures employed deal with someone who breaks in, not with someone who already has access.
i.e. person A with a keys to multiple rooms including the power tools. Now you would like  to lock/tie down a piece of equipment from being moved from Room A to Room b.
Revoking/further restricting Person A's access is a much more straight forward way to achieve your goal.
rayluvsAuthor Commented:
Thank you very much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.