Keep in mind that DTS packages will sometimes be saved with a login and password, and determining which ones will be affected by a changed password is a manual process. You may have to open each DTS package and save them under a new login created for the packages. If you miss one, you will have to reset the login password the package is saved under back to its original password just long enough for you to resave the DTS package under a new login. As you open each package, be sure to check the connection objects to find out what login it uses to connect to SQL Server.
SQL Servers that are currently running replication will usually be affected by a change in the sa password. You need to be ready to rebuild the replication setup if you change the sa password. You can script out the replication, which is a good idea anyway, and rerun the script after you have changed the password.
Summary
There are quite a few companies out there that have very weak login security on their SQL Servers, and determining these weaknesses should be one of your primary concerns when you enter a new environment. This short article has explained several quick ways to audit for those weak logins and start you on the path to understanding your new SQL Server environment.
Next month I will write an article that will help you to determine which logins belong to which server or database role to prepare you for documenting exactly what each login’s permission set is.
Published with express written permission of the author. Copyright 2002 Randy Dyess.
]]>