Secure SQL Server: Identify user issues

Explore the fundamental concepts associated with user logins and permissions. Learn basic methods of logins, users, roles, and groups and the possible management strategies you can use to set up user access to your database.

By Susan Sales Harkins and Mike Gunderloy

With the third installment of our series on SQL Server security, our goal is to give you the tools and the confidence you need to set up SQL Server securely, so that your valuable data is protected from accidental or deliberate theft and corruption. In this article, we'll dig into some of the fundamental concepts that you need to understand when securing your database: logins, users, roles, and groups. All of these concepts are involved in the seemingly simple process of determining just who is asking for access to data or other information within SQL Server.

Miss one of the previous articles?
For more information, read the other articles in the series:

Logins define the users who can connect to a SQL Server installation—not to a particular database, but to a server as a whole. Logins come in two varieties:
  • Windows-integrated logins authorize a particular Windows user or group to connect using their Windows credentials.
  • SQL Server logins authorize a user to connect with a username and password stored by SQL Server.

Which one do you use?
Windows-integrated logins are certainly more efficient and convenient than SQL Server logins because users log in just once—at the network level. Separate logins to the server are unnecessary because SQL Server automatically processes (behind the scenes) the Windows login to permit access to the server. This support is available only if the server is running on Windows NT or 2000; Windows 98-hosted servers must rely on SQL Server logins. If you're running Windows 2000 and you end up supporting a legacy application, keep in mind that SQL Server logins are available only in mixed mode.

Design considerations
You'll want to consider login methods when designing your database—and before you actually start securing the finished product. You should know which authorization method the server uses. Those using Windows 98 servers might want to consider upgrading now if security receives a high priority; that way, they can make use of Windows Integrated security.

The other design issue is knowing who has access, what data they can access, and whether or not they can make changes to objects and data. You don't have to actually have a list of names; you just need the facilities to accommodate users efficiently. To that end, you'll manage security using system stored procedures. SQL Server provides two stored procedures for managing logins:
  • sp_addlogin—Use this stored procedure when using SQL Server Authentication to secure your server. Specifically, this stored procedure creates a new SQL Server login that allows a user to connect to an instance of SQL Server using SQL Server Authentication.
  • sp_grantlogin—This stored procedure allows a Windows 2000 user or group account to connect to Microsoft SQL Server using Windows Authentication.

Only members of the sysadmin or securityadmin fixed server roles can execute either stored procedure.

What's a system stored procedure?
A system stored procedure is a built-in stored procedure that helps you manage the server. You can find a full list of system stored procedures in the MSDN Library. And SQL Server Books Online includes full syntax details and usage examples for every system stored procedure.

While logins belong to servers, users belong to databases. A user ID identifies a particular user of a particular database. Also, users are specific to databases—that is, user Fred in the Northwind database is not the same as user Fred in the pubs database, although both Freds may be associated with the same login.

When you create a user in a database, you associate a particular login with that user. For that database, the login has the privileges of that user. Login IDs need not be the same as user IDs, though, typically, things will be less confusing if you stick to that convention. If a login doesn't have an associated user within a database, a user can connect to that database through a special guest user account.

Special users
All users aren't a permanent fixture within the database and simply don't warrant a user account of their own. On these occasions when you want a user to have temporary access to a database, you can use the guest user account, which allows a login without the usual user account to access a database. The guest user's login must have access to the server and the database must have a guest user account. Once in the database, the guest user is limited to only those activities specified in the guest user account. However, the guest user account doesn't exist by default in a newly created database; the owner or system administrator must create such an account.

Besides guest users, you'll also need to consider the database owner (DBO). This is the user who creates a database. The database owner or system administrator must give permission to create other objects in the database.

To add a user to a database, run sp_grantdbaccess. This stored procedure creates a user in the database corresponding to a specified login. Only members of the sysadmin fixed server role, the db_accessadmin role, and the db_owner fixed database role can execute sp_grantdbaccess.

Roles let you collect users for easier administration. Like users, roles are database objects. For example, you might define a sales role within your purchasing database and make all of the salespeople of your product members of that role. If you then assign permissions to the sales role, those permissions are automatically available to members of the role. Also, a user can be a member of more than one role.

There are three types of roles:
  • Public—This role sets the basic default permissions for all users, and all users are automatically assigned to the public role.
  • Server—Server roles are applicable to the entire server.
  • Database—These roles are applicable to a specific database.

Both server and database roles have predefined categories, which we've listed in Table A.
Table A
Type Name Explanation
Server SysAdmin Assigned members can perform any action on the server.
ServerAdmin Assigned members can set server configuration options.
SetupAdmin Assigned members can manage linked servers and SQL Server startup options and tasks.
SecurityAdmin Assigned members can manage server security.
ProcessAdmin Assigned members kill processes on server.
dbCreator Assigned members can create, alter, drop, and restore a database.
DiskAdmin Assigned members can manage server disk files.
BulkAdmin Assigned members can run bulk insert command.
Database db_owner Assigned members have full access to the database.
db_accessadmin Assigned members can manage Windows group and SQL Server logins
db_datareader Assigned members can read all data in the database.
db_datawriter Assigned members can add, delete, or modify data in the database.
db_ddladmin Assigned members can run DLL statements.
db_securityadmin Assigned members can modify role membership and manage permissions.
db_denydatareader Assigned members can't view data in the database.
db_bckupoperator Assigned members can back up the database.
db_denydatawriter Assigned members can't change or delete data.

Assigning roles
There are system stored procedures for adding roles and members to a database. Use sp_addrole to create a new role in a database. Then, run sp_addrolemember to add user accounts to the role. You can't create fixed server roles; you can only add roles at the database level. Only members of the sysadmin fixed server role, the db_securityadmin role, and the db_owner fixed database role can execute sp_addrole or sp_addrolemember.

Groups provide you with a second way to manage security in bulk instead of user-by-user. Groups do not exist within SQL Server. Rather, they are maintained by the operating system. Using groups lets you tie your SQL Server security strategy to your overall corporate security strategy.

For example, if you have a Windows 2000 group named sales, which contains all of your salespeople, you could create a Windows login specifically for that group in SQL Server. Any member of the sales group will connect to SQL Server as the specified login (unless they have their own individual login, which takes precedence). You can go on to associate that login to a user in any database, and then grant permissions to that user. Any member of the group will get the specified permissions when they go to use the database.

Management strategies
The main goal of user management is twofold. First, you want to make sure that the only users who can get to data are those who really need to work with the data. Second, you should try to minimize the effort involved in user management. Cumbersome policies are less likely to be followed than streamlined ones, to the ultimate detriment of security. Here are some simple guidelines you can follow in user management:
  • Use Windows integrated security whenever possible. This lowers the effort involved in password maintenance and user creation. It also lets SQL Server pass login information to linked servers, which can be important in distributed databases.
  • Manage security at the aggregate level whenever possible. Instead of creating users for every single person who must have access to your data, create roles such as sales or DataEntryUsers. You can then control access by adding and removing role users, while assigning all permissions to the roles. Alternatively, you can manage group membership at the Windows level, and assign permissions to users who represent entire Windows groups.
  • The guest user is dangerous because it grants blanket access to your database for every login on your server. Don't create a guest user in a database unless it will be available to everyone.

While the concepts explored in this article are fundamental to your SQL Server security implementation, they are by no means simple. Understanding the way each login works will go a long way toward determining your overall strategy.