This document provides and introduction for Access developers to getting started developing with SQL Server Security. The most difficult thing is getting your head around how the security works, once you have done that you will be pleasantly surprised at how powerful and simple it is to use. This introduction to security applies specifically to SQL Server 2005/2008/Express.

Note that this is an introductory article aimed at getting you started, and not an article about how to create impenetrable secure databases.

Access developers tend to use one of three approaches to security.

• password protection on a database
• WorkGroup security
• table driven security (having a table of users and looking up what a user is allowed to do)

In Access 2007 WorkGroup security has been depreciated, but If you are used to working with Access WorkGroup security then you will find that an equivalent approach can be used in SQL Server using database roles.

SQL Server and Windows Authentication

What follows uses the SQL Server 2008 Management Studio. If you right-click and view the server properties, you will see that SQL Server supports two general approaches to security.

These security choices are a server level property and if changed the server needs to be stopped and re-started for it to come into effect. Windows Authentication means that users windows accounts or windows groups are mapped through into SQL Server security; this is a fantastic feature. SQL Server security (included in the second option) means that you can create individual logins with passwords which are independent of any windows accounts.

SQL Server (security)

Using this approach you can create one or more logins with passwords and then either allow a user to be prompted when starting the application to provide the username and password, or you can save the username and password inside your ODBC Connection strings and linked tables. You can also create a table called MSysConf in your SQL Server database which controls whether users are allowed to save a password in a connection string (although this technique is rarely used by developers).

A typical example of using this security approach would be where you have an application and you are not worried about being able to know who is using the application, and you do not want or are unable to use Windows Accounts or Groups to manage the security. An example would be in delivering demonstration systems from the Internet, or a low security application in a company.

This approach to security can be extended using several different login accounts to provide different classes of user (see our article link on integrating asp.net security at the end of this document for some sample management code). You could even combine this with a table driven approach to security.

Windows Authentication (security)

With Windows Authentication you can register each individual windows user as a Login to SQL Server and manage at a very fine level what they are able to do. But this does mean that when adding a new user they need to be added into the SQL Server security.

One of the most useful features I find on SQL Server is rather than adding in individual users, is to add in one or more Windows Groups, and assign security to the Windows Groups. This has the advantage that the security can be simply tied into managing the general network security. Now the really clever feature of SQL Server is that even when managing the SQL Server security with windows groups, SQL Server still provides functions to find out who the individual users are.

Security at Server Login and Security in each Database

The first idea to grasp is that security is initially managed at two levels. The first level is the Server Login. Each account (note a windows group is an account) needs to be added into the Server Logins.

Then the second level of security is controlled on each individual database on the server.

The easiest way to add the security for a database is to do this at the Server Login Level using the database map. What then happens when you give an account permissions on an individual database is that SQL Server will automatically create an entry in the Users section for the database. Note that is you delete an account you will need to go into the individual databases where the user has been registered and delete those items in the Users folder.

Adding a New Login

If you right-click on the server logins and select new Login, you will see the following dialog, where you can select the windows user or group account, or create a new SQL Server account. Don’t overlook clicking on the search button to find the account.

The User Mapping tab allows you to grant the account access to a particular database.

Once you click to Map a particular database, the bottom part of the screen will allow you to decide on which database roles the user should have access to.

If you have a simple application, and you are not worried about users being able to get at your SQL Server through any design tools, then you can check the db_owner box. This gives the user full database owner and design rights on the database.

Personally I don’t do this except in test systems (to make life easy). But if your users are not likely to get hold of a copy of the Management Studio or understand how to write a pass-through query to modify the database you could use this very crude approach to security.I would recommend that you read on and get to understand database roles.

Database Roles

A database role is a unit of security in your database to which you can map the accounts registered in the SQL Server. Then you can set more complicated permissions on each database role. In the Security tab, inside a database right-click to create new database roles (note there are also a number of built-in roles not shown below).

Next we need to assign permissions to each of the database roles.

Schemas are another fantastic feature in SQL Server through which you can group together objects such as tables and views into logical groups. The schemas all you to partition an application into manageable chunks, and they also make managing security easy.

You will notice a built in schema called dbo. This stands for database owner, and if you do not create your own schemas everything will be in the dbo schema.

The simplest approach to managing the permissions a database role can have on a schema is to right-click on the schema and go to the permissions tab.

Next we search and add in each of our database roles. Then we set the permissions for each role.

Shown below we have given our ReadOnly users the ability to select data, and also to execute any stored procedures.

Next for our GeneralUser we have allowed them to Select, Insert, Update, Delete and Execute.

Back to the Login

Now returning back to our Login, we can allocate the Accounts to different database roles.

Security Function : SUSER_SNAME()

There are a number of functions in SQL Server which allow you to identify information on users, the SUSER_SNAME() function returns the users domain\windowsaccount. Equipped with this information your application can then control menus, forms and other activities that a user can perform. This function is also useful for providing auditing information by stamping a record using a trigger to indicate when a record has been altered. Below are some simple examples of using this and the IS_MEMBER() function.

SELECT SUSER_SNAME() GO CREATE PROC usp_UserName AS SELECT SUSER_SNAME() GO exec usp_UserName GO SELECT SUBSTRING(SUSER_SNAME(),(CHARINDEX('\',SUSER_SNAME()) + 1),LEN(SUSER_SNAME())) go SELECT IS_MEMBER('Public') GO CREATE PROC usp_CheckMembership @DatabaseRole VARCHAR(128) AS SELECT IS_MEMBER(@DatabaseRole) GO usp_CheckMembership 'Public' GO

Further Reading

The following link takes you to our article describing how to use schemas to partition your database design Building applications with schemas and database roles

This next article although dealing with .net security, contains a download with sample code which gives an indication of how to use program code to manipulate SQL Server Security Integrating ASP.NET security with SQL Server security using LINQ

If you lookup ‘principals’ in the SQL server help system you will find out a lot more security terminology that has been covered in this document.

Top of Page