In today’s fast-paced world, organizations are constantly looking for ways to streamline their processes and automate repetitive tasks. One such task that often arises in SQL Server environments is the need to add a user to multiple databases within an instance. In this article, we will explore a script that can help you achieve this automation.

Imagine a scenario where an organization’s data gets audited once a year. During this time, auditors require db_reader privileges to access the databases. Manually adding the user to each database can be a time-consuming and error-prone process. To address this challenge, we can leverage the power of SQL Server scripting.

The script we will discuss uses an undocumented command called sp_MSforeachdb. This command allows us to iterate through all the databases within an instance and perform actions on each one. Here is an example of the script:

EXEC sp_MSforeachdb '
DECLARE @name VARCHAR(500)
SELECT @name = ''?''
IF ''?'' NOT IN (''tempdb'')
BEGIN
    USE [?]
    IF DATABASEPROPERTYEX(''?'',''Status'') = ''ONLINE'' AND DATABASEPROPERTYEX(''?'',''Updateability'') = ''READ_WRITE''
    BEGIN
        IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE name = ''SQLAuth_Auditor'')
        BEGIN
            CREATE USER [SQLAuth_Auditor] FOR LOGIN [SQLAuth_Auditor]
            PRINT ''Added User for ?''
        END
        EXEC sp_addrolemember ''db_datareader'', ''SQLAuth_Auditor''
        PRINT ''Added db_datareader for ?''
    END
    ELSE
        PRINT ''SKIPPED the ? database''
END
'

Let’s break down the script to understand how it works. First, we declare a variable to hold the name of each database. We then check if the current database is not the tempdb, as we don’t want to perform any actions on it.

Next, we use the USE statement to switch to the current database. We then check if the database is online and has read-write access. If these conditions are met, we proceed to check if the SQLAuth_Auditor user already exists in the database. If not, we create the user and print a message indicating that the user has been added.

Finally, we use the sp_addrolemember stored procedure to add the user to the db_datareader role. This role grants the user read access to the database. We print another message indicating that the role has been added.

Now, you might be wondering if there is an easier way to achieve this task. One possible approach is to add the user to the Model database. This way, any new databases created will automatically inherit the user. However, there is a catch. If there are no databases currently in the instance, the Model database will not be used as a template, and the user will not be added to any databases. This is where the script we discussed earlier comes in handy, as it ensures that the user is added to all existing databases.

In conclusion, automating user addition in SQL Server can save time and effort, especially when dealing with multiple databases. The sp_MSforeachdb procedure provides a convenient way to iterate through databases and perform actions on each one. By leveraging this script, organizations can easily add users to all databases within an instance, ensuring compliance with auditing requirements.

Have you used the sp_MSforeachdb procedure in your environment? What other use cases have you successfully implemented using this command? Let us know your thoughts and experiences in the comments below!