What Security Model Will Be Used
In Microsoft SQL Server, you have three choices when it comes to data security: standard security setting (default), integrated security, and mixed security. The standard security setting requires each user to supply a valid login ID and password to attach to the server. This validation is separate from the network login scheme. This setting supports connections by non-Windows NT validated users accessing your data server. Integrated security allows you to use the network login and password supplied to Microsoft Windows NT as a security mechanism for access to your data server. If users are validated with a login and password by Microsoft Windows NT, they can connect to the server. This provides you with the one login, one password scenario that many companies are looking for. Keep in mind that just because a user can connect to the server does not mean he or she has access to your database. Mixed security is used when you want your Microsoft Windows NT users to supply only one login and password to be on the network and connect to your server. This method would also allow other network users to connect to the server as long as they can provide a valid database login and password. In the mixed-legacy environments of today’s businesses, this is a very popular method of implementing security. Microsoft SQL Server uses an interesting security model that has two levels of security. First, you must be allowed to connect to the server. Then, for each database you are granted access to, you are granted rights and permissions on a case-by-case basis. To explain this concept using our office example, say that you have been given a key to get into the office building. This key gives you the right to enter and walk through any hallways and public areas to function and find your way around. Then, for access to certain areas in the building, you need an access card (or permission) to get into each office or room (database) you do business in. If you are not granted access to, say, the Human Resources department, you simply cannot access this area (database). By assigning security on a departmental level, you can give your users freedom to do their jobs while protecting sensitive data from people who should not see it. This model is very good for a few reasons. In a lower-budget design, you can have both the production databases and training or development databases coexist on the same server. You don’t have to worry about adding to an existing system and having users gain rights by association to other databases. Users are restricted by default and granted access by the owner of the database to do what they need. No one except the SA (system administrator) has rights in a database unless they own it.