1

I want to implement a database security on the database i created so no one can access it from SQL Server Management Studio, and that it will be only accessible through code.

Is that possible? and if it's not is there any other ways to prevent user from accessing database?

3
  • 4
    They're always only accessible through code. SQL Management is written in code!
    – Jon Hanna
    Oct 26, 2011 at 12:50
  • You can use commercial database security products, such as GreenSQL, greensql.com, which is also free.
    – user4216
    Nov 2, 2011 at 16:25
  • Are you asking about how to implement security in sql server or how to block a particular application (ignoring that a dedicated user can always just use a different application)? Nov 3, 2011 at 12:08

7 Answers 7

7

Well at least DBAs should be able to connect to such database using SQL Management Studio, or nothing would be possible like backup/restore/checks...

just do not give anybody the sa password and create a user for your application and configure it accordingly and also, do not give anybody the password of this application user.

1
  • Please note that it is still possible to do backups/restore/checks without using SQL Management Studio. Oct 28, 2011 at 20:14
4

You can't specifically turn off access through SQL Managment, but if you set up proper security by assigning only the necessary rights to authorized users, then anyone not authorized to open the database won't be able to get into it through Management Studio or code.

0
3

Basically impossible for desktop clients.

Security when you connect to the database doesn't care about what client is used, SSMS is just another client.

Every PC with Office installed since Office 95 at least has msqry32.exe which is a client. You have the ODBC Control Panel item too.

Note: the APP_NAME() function in SQL Server returns what is in the Connection String which can be set with anything.

3
  • Couldn't he create a login trigger that rejects connections from SSMS? Oct 26, 2011 at 15:03
  • @Nick: yes. But still doesn't secure SQL Server as such, only blocks SSMS. You can still use SQL Server 2000 Query Analyzer to connect too.
    – gbn
    Oct 26, 2011 at 15:12
  • True. A login trigger would be the naive solution to the asker's question. :) Oct 26, 2011 at 15:14
2

The broad issue sounds like you're using integrated (trusted) securities in connection strings.

If you configure your application to run with a specific log on then you can allow that access and disallow the users rights on the DB.

If you app is a website this is easier as you can configure the AppPool in IIS to run with whatever identity you require.

In a windows app it may be more tricky - but I think you can set the credentials to use for the process to be other than the user that ran the app. Alternatively you may need a middle tier.

2

You can think of SQL Server Management Studio as a regular database application that opens a connection to your database and runs dynamic SQL commands. If you don't want people running commands, create logins with limited access for most people and save full privileges for DBAs.

Just as a side note, if you want to see who is logged in via SQL Server Management Studio:

select *
from sys.dm_exec_sessions
where program_name = 'Microsoft SQL Server Management Studio'
1

There are lots of ways of preventing/restricting particular users access to the database, what you're touching on is SQL Server's Security model, some good resources to get your head around are:

A fairly quick summary to achieve some of what you're asking:

  1. Create a Server Login - this allows a user or group of users (if using windows authentication) to connect to the SQL Server, it does NOT allow them to see or run queries against any of the databases yet:

ie:

CREATE LOGIN [testUser] WITH PASSWORD=N'Blah1234' MUST_CHANGE, 
    DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=ON, 
    CHECK_POLICY=ON
  1. Add that login to a database - this will allow those user(s) to connect to a particular database, at this point still not able to do anything other than see that the DB exists:

ie:

USE [MyDatabase]  
GO  
IF NOT EXISTS (select 1 from sys.database_principals sdp with (nolock) 
    where lower(name) = 'testUserDB')  
    CREATE USER [testUserDB] FOR LOGIN [testUser]  
GO
  1. Allow user to do something - This is where it gets into the guts of your question, regardless of what application the user is using (SQL Server Management Studio, MSAccess via linked tables, Excel Data connection, etc...) they can only do what you now give them access to.

So if you want to only give them access to view particular data:

GRANT SELECT ON [dbo].[MyViewOrTable] TO [testUserDB]

or you might want to only give them access to execute certain stored procedures:

GRANT EXECUTE ON [dbo].[MyProcedure] TO [testUserDB]  

There is a lot more too it, but hopefully that will get you started, the first thing to remember is that if you block a single application there is nothing to stop those users from using another app, instead ignore how they connect to the db and focus on what they should be able to do on the DB.

0

You have to setup the permissions so that only one SQL Server account has access, and only you know the password to that account. That's the only way. Or, use impersonation, and only give that one person's windows account access.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.