Understanding SQL’s security and permissions is not a trivial task. Many of your choices come in pairs, firstly there are two overall strategies or approaches:
a) Develop and refine your vision of who should see what information.
b) Check all the places where you can set security and permissions in SQL.
Then test by logging on as ordinary user and see what information you can see.
Security Checklist:
- Logon to SQL Server – Server Roles
- Logon to Database – Database Roles
- What to do with the guest account?
- Take care in setting ‘public’ permissions
- Make good use of Views and Stored Procedures
- What should the public permissions be. N.B. Northwind’s public permissions are not typical
Logon to SQL Server
If you are new to SQL there are two concepts to be clear on. Firstly, you must logon to both the server and each database you require access. Secondly there are two ways to be authenticated, Windows Authentication or SQL Authentication.
My recommendation is to use Windows 2000 authentication where ever possible. Avoid users having a separate, extra SQL Login account. Stick with Windows Authentication only, which you will find by: right-clicking the Server Object in the Enterprise Manager, Properties, Security tab.
Logon to Database
Once you have been authenticated at the server level by default you will only have Guest access to each database. The administrator needs to grant roles to the user for each database they have been authorized to access.
Select the database object, then double click roles in the right hand pane and check out the names. db_owner is the ‘all powerful’ group. It is common to assign users to db_datareader or db_datawriter roles.
