Like many things, databases contain a method of keeping data secure. Where the operating system handles security at a file level, SQL Server handles security at a structure level and allows you to configure this partially at server level and database level.
To allow access to a database, a user needs to be created at both server level (to allow access to the server) and database level (to allow access to the database). Both of these levels offer different rights to perform various actions. The easiest way to think of this is the server level allows you to tell the server to perform a task (for example create a database, perform a backup or even add new users) whereas the database level covers permissions specific to that database (for example creating tables, reading data or modifying the database structure). With this in mind, for a user to access a database on a server, the user also needs certain access rights on the server as well (the user needs to be able to ask the server to access the database).
When it comes to users, you have two options. The first is to use Windows Authentication; the second is to use SQL Authentication. The former ties your newly created user to the Windows authentication system to ensure that the user is authenticated. This has nothing to do with what they can access on the server; it is only to allow the server to check that you are who you say you are. This means that when you connect to the server, you do not need to provide a password because Windows will tell the server that you are authenticated. The latter does not use Windows at all and instead asks the user to authenticate the connection to the server by providing log-in credentials directly (including password).
Each of these has its pros and cons. Using Windows Authenticated allows for a level of security when it comes to accessing the server as it does not require the login credentials to be passed with making the initial connection. It also allows administrators to cut a user off at the source (i.e. Active Directory) without having to visit each SQL Server instance. The SQL Authenticated allows users to connect to a database when there is no guarantee that the user will be logged in (for example you have a publicly available database and clients that do not authenticate with your network).
It is very important that a user only has permissions to perform operations within their role. Some would argue that the application should govern that and to some extent they are correct. But imagine that the database is accessed with malicious intent either via a back door in the solution or through another method. If you have just gone ahead and given your user System Admin access to the server or DB Owner access to the database when they only need to be able to execute stored procedures, imagine the damage that someone could do either straight away or later by adding their own user to the system. What seemed like the ‘easy thing to do’ could soon become the ‘worse thing you could have done’ – after all, you wouldn't make a range of cars that all use the same key!
Hopefully this post will have given you something to think about when managing security on your SQL Server. You can do a lot with the security levels and can limit access rights for a user to a very specific level. When doing this just keep in mind that someone needs to manage this. It’s not so bad when you are creating a database that is access by another server (i.e. a website) but when you are looking at a client application that is accessing it and each user has their own credentials (either Windows or SQL) then setting access rights to the nth degree may not be such a great idea.
That’s it for this post. I hope this post is helpful and as usual, constructive criticism and suggestions are welcome.