How can I get history of logins in SQL Server

Russel Loski 421 Reputation points
2021-06-14T15:21:44.343+00:00

Here is the request. Someone wants to know what SQL Server logins were mapped to sysadmin on April 1, 2021. How can I do that?

These are the approaches that we considered:

1) Replace the master database on an existing development server. I assume that server_principals and the server role mappings will then show their April 1, 2021 values.

The question that I couldn't answer about this approach was what would happen if the SQL server has had an upgrade/security patch since April 1, 2021. Would the server be able to work with a master database that is from a previous build?

2) Restore the master database from April 1, 2021 to a new user database (Master2).

The problem that we have with this is that the views we would use to get login information (server_principals, syslogins) always gets the values for the server as a whole and not any values from master2. Is there a view that I can use to read this information from master2?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,817 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
Accepted answer
  1. CarrinWu-MSFT 6,856 Reputation points
    2021-06-15T06:08:53.517+00:00

    Hi @Russel Loski ,

    Welcome to Microsoft Q&A!

    The question that I couldn't answer about this approach was what would happen if the SQL server has had an upgrade/security patch since April 1, 2021. Would the server be able to work with a master database that is from a previous build?

    The upgrade/security patch will not change your data in SQL Server. And we don't have too much permission to modify master database. Please refer to master Database to get more details.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-06-14T20:42:20.257+00:00

    I highly recommend against ever overwriting master, unless it is a bare-bones effort to restore.

    I would suggest you restore master to a new name.

    In the future, you can use an audit to track change to membership.
    https://solutioncenter.apexsql.com/auditing-security-changes-in-sql-server-2/

    1 person found this answer helpful.
  2. Erland Sommarskog 101.9K Reputation points MVP
    2021-06-14T21:42:02.843+00:00

    The question that I couldn't answer about this approach was what would happen if the SQL server has had an upgrade/security patch since April 1, 2021. Would the server be able to work with a master database that is from a previous build?

    I think it would. Then again, I would not clobber an existing instance (unless it is on a VM of which I take a snapshot first), but rather install a new instance on a throw-away VM. And in that case, try to get the matching version, just in case.

    One additional challenge to restoring the master database. The backup was taken from the default instance and is being restored to a named instance. So, all of the files are not in the correct place, including the system database files.

    But if all you want to look at is data in master, you can start SQL Server with -f, since all you need is master.

    In difference to Jeffery, I don't have objections to the idea as such. I can an imagine an investigation going on. Or security auditor trying this as an exercise.

    I can see other problems though. What if the membership changed during the day? And in the end you may also have to dig in the AD, if there was membership granted to AD roles.

    1 person found this answer helpful.
  3. Russel Loski 421 Reputation points
    2021-06-15T15:14:10.053+00:00

    I keep telling my manager that I shouldn't be doing this since I am primarily a BI developer (SSIS, PowerBI, SSRS), especially since there are maybe 40 others in our organization for whom this kind of request is their bread and butter. But a person's gotta do, ...

    The copy of master I was able to restore to a default instance SQL Server (this is a throwaway instance, so if something breaks, no biggy). The SQL Server was RTM, but the master database was 2019 CU 9, so we had to upgrade the server to CU 9.

    Finally, the master was pointing to system database file locations that were not valid (actually only the tempdb location was bad). So I used a trace flag (3608) that loaded just the master database. I created the folder needed for tempdb. The final piece, which I didn't know about, was that I had to use the SHUTDOWN tsql statement in my on sqlcmd connection to stop the server. Then I was able to restart the server in normal mode. Of course, none of the user databases are where they should be, but that is trivial even for a BI developer.

    0 comments