Secure Your SMS in 6 Steps

Steps to Take
Now that you understand the basics of SQL Server security and how it interacts with SMS security, you are ready to take the necessary steps to secure your SMS system. Securing SMS involves six steps. You use SQL Server's EM to complete step 1 through step 3 and SMS Security Manager to complete step 4 through step 6. Here are the six steps:

1. Select Logins from the Manage menu. Using the Manage Logins dialog box, create a login for each user who needs to access SQL Server. In addition, create a login for each role you want to put in the database. (For example, in Screen 1, I created the login Helpdesk.) Set the default database to SMS and fill in a password.
2. Select your database from EM. Select Users from the Manage menu. Create database users for the organizational roles you want to use in your SMS database. Before you can create an alias, you must first map the login to the database user. (I created a login for each role.) Map database users to logins that don't refer to an existing person. If you use one of your regular logins (i.e., one that refers to a real employee), you'll need to change this mapping as soon as that employee leaves the company. Using a role-related login is a more generic approach.
3. When you add a user to the database, the lower part of the Manage Logins dialog box becomes available. From here, you can create as many aliases as you want. However, you're restricted to extending a login to one database user. If you attempt to extend the same login to two database users, Manage Users will remove the login from the previous database user's list of extended logins. Click Modify to save your changes. Screen 2, page 175, shows the list of extended logins I created.
4. Start the SMS Security Manager and log in using the systems administrator login or another login that has DBO equivalence in your SMS database. Notice all the security objects you can set. Table 1 (an excerpt from Microsoft SMS Books Online­BOL) provides a detailed description of these objects. For the selected user (e.g., DBO), all objects are accessible. For a new user, the default setting on all objects is No access.
5. Select a user from the SMS Security Manager menu. Screen 3, page 175, provides an example of this menu. Notice that all objects are closed for this user. You can now change security settings for this user to View or Full access.
6. Consider using one of the security templates that come with SMS Security Manager. (Even if you don't intend to use these templates, you might want to look at them.)

Now you've set up security for SMS. However, before you begin using SMS, you must carefully determine which user needs which type of access to the database. In addition, you must determine what roles are necessary in your SMS environment. Some users might need to access the database in different roles (e.g., Helpdesk and Job Manager). You can either let some roles include others (e.g., job manager includes Help desk employee) or create a new role user (e.g., Job Manager plus Helpdesk). Whatever you do, always set up a convention to manage your users and use that convention rigorously.

SMS security isn't hard to implement. However, it's a key factor in protecting your SMS environment and maintaining the integrity of your inventory database. Considering the importance of the inventory database for all other SMS roles, you need to take security seriously.