[dba-VB] [AccessD] I'm getting nowhere

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