Unanswered: Read this first! FAQ for DBForums Microsoft SQL Server forum
The current version of Microsoft SQL is SQL 2014.
If you are running a different version of Microsoft SQL, it is your responsibility to state what version you are using (for example, SQL 2000, service pack 2). If you don't include information about what version of Microsoft SQL you are using, we'll assume that you are running SQL 2012 and the answer that we give you may or may not work with the version of Microsoft SQL that you are using.
Kudos to r937 and Thrasymachus for reminding me to include this version information in our FAQ
An FAQ is a list of Frequently Asked Questions in a forum, newsgroup, or other (presumably online) place. These are questions that regularly appear because pretty much everybody asks them at one time or another. This is a really good place to look for a general "look and feel" for both the forum itself, the people who post there, and the topic in general.
In discussion of poor practices in another forum, we unearthed some old threads here that demonstrate the wrong way to ask for help. If you want examples of how NOT to ask for help, these are some good examples!
With thanks to Blindman for bringing these posts back to public view, and to R937 for suggesting that they be imortalized!
1) If you are asking questions for a course, please say so up front. We need to take a different approach when helping you with homework than we do with folks that simply need an answer to a "real world" problem.
2) It helps us a great deal if you can post a link to the assignment, or if you can scan the assignment and post it with your questions. That way we know exactly what the assignment requires, and we can infer a lot about what they're trying to get you to learn in the assignment.
3) Don't expect us to just do your homework for you. Life isn't like that, and you'll cheat yourself more than you can cheat the school/teacher if all you do is copy what someone else has done for you. We'll be glad to help, but you really don't want to turn us loose on your homework assignment... We can be evil!
4) If you have a partial solution worked out, or have at least tried something, post that too. If we can see what you've tried, we can probably help you a lot more than if we "start cold" because we can then see more of how you're thinking and where we can help.
There are two parts to SQL Server security, and both parts are required... They work together to determine security, and neither is useful without the other.
The database server has logins. Logins establish identity and grant access to the server. Logins come in two general flavors, Windows Authenticated and SQL Authenticated.
Windows Authenticated logins use trusted connections to convey information about the Windows Login that is making the connection. The user doesn't need to enter any information like username or password because these were entered and confirmed when the user signed on to Windows. Windows Authentication is normally only used in an Active Directory environment, although it is possible to use the Windows logins provided by the local machine too. Windows Authentication is more secure at the communication level (between the client and the server), and can be more secure end-to-end if it is used properly, but applications that use only Windows Authentication are vulnerable to "drive by users" if a machine is left unsecured.
SQL Authenticated logins require passing the user name and password to SQL Server. The SQL engine then confirms the user exists and that the password is correct for the specified user. SQL Authenticated logins are especially good for use with very old applications and also for non-windows applications.
Once the user has logged in, their identity is established for SQL Server. Some logins have server administrative permissions, such as the sa login or the sysadmin role which can grant them access to everything on the server. Each login is logically mapped to at most one user in each database. If no user is mapped to a login, then that login has no access to that database.
Within their database, a user is granted permissions. Some permissions are database-wide, such as db_owner or db_datareader. Other permissions are specific to a given object such as a view, table, or stored procedure. Permissions control what actions a given user can perform. If permission is not granted (directly or indirectly), a user can do nothing within the database.