You need SQL Server security to make your SMS system secure
System security is one of the least documented aspects of Systems Management Server (SMS). However, an unsecured SMS system can definitely lead to problems. The default access level for anyone using the SMS Administrator tool is the same as the SQL Server systems administrator login. The systems administrator login gives you Database Owner (DBO) permissions on the database. In other words, SMS out of the box gives users unlimited access to its resources and, thus, full control of its mechanisms.
Consider this scenario. A Help desk employee develops an interest in the SMS Administrator tool he works with every day. Because Windows NT system resources aren't available to him, he expects that the same will be true with SMS. Imagine his surprise when he finds he can easily set up a mandatory job (e.g., send Microsoft Office 97 to all clients at all subsites in the SMS hierarchy). Imagine your surprise when your network clogs up as a result.
You can avoid this scenario if you secure your SMS system. SMS security isn't difficult to plan or implement. However, SMS depends heavily on SQL Server; thus, setting up SMS security requires a basic understanding of SQL Server security and how it interacts with SMS security.
SQL Server Security
SQL Server's standard security mode requires a user to log in manually each time that user establishes a connection to the server. (For more information about the standard security mode, see the sidebar "SQL Server Security Modes and SMS.") When you log in to a SQL Server database, you must enter a SQL Server login and the name of the database you want to access. A SQL Server login is a type of user account. SQL Server uses two types of user accounts: login and database user. SQL Server's Enterprise Manager (EM) creates logins independently of any database and gives a user access to SQL Server as an application. SQL Server's EM creates a database user as part of a database and gives a user access only to the database the user belongs to. Typical permissions for a login are to create a database or to change SQL Server configuration settings. For a database user, typical permissions are to insert, view, or delete database objects (e.g., tables and views).
SQL Server's EM maps logins to database users. Therefore, when you log in to SQL Server using your login and a database name, SQL Server knows which database user to use. Because a network user uses a database user to define permissions within the database, SQL Server knows the appropriate security context for this database connection.
How SMS and SQL Server Security Interact
Typically, if you want to set up security requirements for multiple users, you add the users to a group and arrange security requirements for the group. However, the SMS Security Manager doesn't recognize SQL Server groups. The SMS Security Manager accepts only database users to set security. Thus, if you work with groups, you still have the cumbersome task of defining security rights for each database user.
A workaround to this problem is to use aliases instead of groups. An alias is an extension of a mapping from a login to a database user. After creating a map, you can tell any number of logins to share the same database user. So, if you create one database user for each role in your SMS database (e.g., Helpdesk, Administrator) and map a login to that database user, you can extend as many of your other logins to this mapping as necessary to ensure that they all receive the same permissions.