Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Oct 26 '11 at 13:15

This question came from our site for professional and enthusiast programmers.

4  
They're always only accessible through code. SQL Management is written in code! –  Jon Hanna Oct 26 '11 at 12:50
    
You can use commercial database security products, such as GreenSQL, greensql.com, which is also free. –  user4216 Nov 2 '11 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)? –  Andrew Bickerton Nov 3 '11 at 12:08
add comment

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

share|improve this answer
    
Please note that it is still possible to do backups/restore/checks without using SQL Management Studio. –  StanleyJohns Oct 28 '11 at 20:14
add comment

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.

share|improve this answer
    
Is that possible in sql windows authentication ? –  user733659 Oct 26 '11 at 12:50
3  
What part of "You can't specifically turn off access through SQL Managment" did you not understand? authentication != authorization. –  TomTom Oct 26 '11 at 12:51
add comment

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.

share|improve this answer
    
Couldn't he create a login trigger that rejects connections from SSMS? –  Nick Chammas Oct 26 '11 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 '11 at 15:12
    
True. A login trigger would be the naive solution to the asker's question. :) –  Nick Chammas Oct 26 '11 at 15:14
add comment

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.

share|improve this answer
add comment

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'
share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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