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