After finding your SQL Server instances in chapter 6, creating an inventory in chapter 7, and adding them to a Registered Server or Central Management Server in chapter 8, you are now ready to deal with users and logins. Ensuring that our business users and applications can successfully connect to the databases that they require is a good way to address issues before they happen. This reduces the time a DBA has to spend resolving issues after they cause problems, or prevent them altogether.
In this chapter, we are going to show how you can simplify the work that is required to administer instance logins and database users by following some common DBA stories around logins. This chapter will take a bit of a different path, because it’s told as a story. We thought that for logins, seeing real-world scenarios would be the most effective way to teach this topic. And by following along with these scenarios, you will learn how to do the following:
- Read the error log to find the issue.
- Create new logins and users.
- Identify and repair orphaned users.
- Sync logins across availability group replicas.
- Use source control to control user account changes.
- Export a T-SQL script of your users.
- Identify the way that a user gained access via nested Active Directory groups.