Single Person Security Example
In the simplest possible security system, a single person is responsible for all aspects of the database and will be its sole user. This hypothetical user (Tom Brown in London) must be able to:
- Create the database and its tables.
- Write programs that interface with the data.
- Load and maintain data.
- Produce reports.
The users-to-activity map for this example lists the single user and the activities he needs to perform.
| User account | Activity |
|---|---|
| LONDON\tombrown | All database access |
The first step in creating a security system is to add a Microsoft® SQL Server™ login for LONDON\tombrown, allowing him access to SQL Server. Because the predefined sysadmin role contains all permissions necessary for this user, the LONDON\tombrown SQL Server login should be added as a member of the sysadmin role. When LONDON\tombrown connects to an instance of SQL Server, SQL Server calls back to Microsoft Windows NT® 4.0 or Windows® 2000 to authenticate the connection. If it is validated, the connection is accepted, and he is allowed to perform activities based on the permissions associated with the sysadmin role.
If Tom Brown did not have a Windows NT 4.0 or Windows 2000 login, he could be given a SQL Server login. In this case, an instance of SQL Server would need to be running under Mixed Mode, which allows users to log in under Windows NT 4.0, Windows 2000, or SQL Server logins. A login named tombro could be added to SQL Server independent of the Windows NT 4.0 or Windows 2000 login, and tombro could then be added to the sysadmin role. When the user logs into Windows NT 4.0 or Windows 2000 and attempts to connect to an instance of SQL Server, he must specify the tombro login name and password that SQL Server knows.