SQL SERVER – How to Add User Across Databases

In this era of standards, many organizations want to be using some of the cutting edge technologies in their organizations. In many of these interactions I was lucky enough to meet few DBA’s who give me interesting challenges to meet within their organizations. I still vividly remember sharing a script with a DBA who reached out to me to know how we can add a user to all the databases within their SQL Server instance.

On first look, I asked “why?” The answer was simple. Their organization data gets audited once a year by the auditors and they need to be given db_reader privileges during the time of audit. So they wanted to make sure this is done in an automated fashion. I was immediate to give them recommendation to my Plurasight Course. But they were not on the latest version and wanted something in previous versions.

So here is the script that I gave them:

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 '


The script uses an undocumented command sp_msforeachdb which I have used before in earlier blogs too to achieve some interesting requirements.

Here in the script, we check of SQLAuth_Auditor user existence in all the databases. If it is not available, we go ahead and add the db_datareader role to the database.

My ask: How many of you have used sp_MSforeachdb procedure in your environment? What are the use cases where you have successfully used the same.

Quick Quiz:

To achieve the above task, can’t we just add the user to Model database and all databases created will automatically get the user added? Is there any catch provided there are no databases currently in a given instance? Do we still need a script like this? Let me know your thoughts.

Reference: Pinal Dave (https://blog.sqlauthority.com)