Secure SQL Server: User identification issues

In our article on the SQL Server Security series, our goal is to provide you with the tools and confidence you need to securely install your SQL Server, so that your valuable data is protected from unintentional or intentional sabotage or theft. In this article, we'll delve into some basic concepts that you need to take advantage of to secure your database: logins, users, roles, and groups. All these concepts need to be used to determine who is requesting access to data or other information in the SQL Server that looks simple.

Login

Logins specify which users can connect to a properly installed SQL Server-not a particular database, but the entire server. There are two different forms of login:

Windows integrated logins that authorize specific Windows users or groups to connect using their Windows trust books.

SQL Server logon, which authorizes the user to connect using a username and password saved by the SQL Server.

Which one should you use?

Windows integrated logins are certainly more efficient and convenient than SQL Server logins because users need only log in once-on the network level. It is not necessary to log on to the server separately because the SQL Server will automatically process (in the background) Windows logon, allowing access to the server. Support for Windows logon is only valid if the server is running on Windows NT or 2000, and servers installed on Windows 98 must rely on SQL Server logins. If you are running Windows 2000 and you eventually need to support your legacy applications, keep in mind that SQL Server logins are only available in mixed mode.

Design Considerations

When designing your own database, you need to think about how to log in--and before you really start to secure the final product. You should know which licensing method the server is using. Those who still use the server on Window 98 may want to consider upgrades now, if security is an important issue, and then they can use Windows Integrated Security (policy).

Another design issue is knowing who has access, what data they can access, and whether they can make changes to objects and data. You don't really have to list the names; you just have to use the tools to help your users effectively. Finally, you should use the system's stored procedures to manage security. SQL Server provides two types of stored procedures for login management:

sp_addlogi--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 the user to use SQL Server Authentication to connect to an instance of the SQL Server.

sp_grantlogin--This stored procedure allows Windows 2000 user or group accounts to connect to Microsoft's SQL Server using Windows authentication.

Only members of the sysadmin or securityadmin intrinsic server role can execute these two stored procedures.

What is a system stored procedure?

The system stored procedure is a built-in stored procedure that can help you manage the server. You can find a long list of system stored procedures in the MSDN Library. SQL Server Literature online captures all syntactic details and usage examples for each system stored procedure.