Stuart McLachlan
stuart at lexacorp.com.pg
Sat Feb 19 18:22:49 CST 2011
On 19 Feb 2011 at 14:53, jwcolby wrote: > If I could find something that started at the > "This is SQL Server security" basics I could learn this stuff. Maybe this will help. (This is "my" understanding of it - corrections from others welcome .) There are two levels of "Security" in SQL Server: 1. SQL Server Instance (Server name) level 2. Database level At the Instance Level, you have: 1. Server Roles 2. Logins At the Database level you have: 1. Database Roles 2. Users INSTANCE LEVEL ============== SERVER ROLES These are generic sets of "rights" which apply to the entire Instance. "Server role is used to grant server-wide privileges to a user" . Generally, use Public for all logins unless you need admin rights on the server. LOGIN To allow anyone to access SQL Server, you need to create a login at instance level for them and then define what that login can do in terms of individual databases Login = an entity that can log in to SQL Server. In your situation, you are using SQL Secruity so a Login needs a Username and Password. If using Windows/Mixed security, it could also be an Active Drectory user. Note the use of the word "entity" - not person. With SQL Security, an entity is entirely identified by the username/password pair. If you embed a standard username/password in a connection string for an application that connects to SQL Server, then that application itself is the logged in entity. Alternatively, if you collect the username/password from the person using that application and put that the in the connection string, the individual user is the entity. On creation, you can define the "Default Database" for the login - this is the one they automatically access (so your don't need to specify it in your connection string.) DATABASE LEVEL =============== DATABASE ROLE Role = a definition a what entities with that role can do in the database.. There are a number of predefined roles, which are useful for things like "read only" users but you frequently need to create your own and assign rights to specific database objects for that role. i.e. allow read only on some tables and write access on others. You can also do things like prevent users from directly writing to any tables and only allow them to run specfic stored prcedures to update data. Once you have defined a new role within the database, you can assign that role to specific users within that database. You can think of a role as similar to a Group, it defines a set of rights and you can assign roles to users in the same way you assign "group membership" to Windows users. USER Once you have created a Login, you go the relevant database ( or databases) and assign rights to that login in that database. You do that by adding the Login as a User in that database. User = The definition of what a particular login entity can do in the database. To make a specific login a user in the database, you create a new user and select the existing Login name. Note that you can give that user the same name as the Login name or use a completely different one. Unless you have a good reason, I'd use the same as the login name. You then assign Databse Roles to the User to control what the user can do in the database. -- Stuart