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)
9 Comments. Leave new
Hi Pinal,
Thanks for awesome post !!
We used sp_MSforeachdb to grant access on all databases for around 10 SQL instances. We did it by creating local sever group and running almost similar script with less checks. Luckly it worked.
Per my understanding, below is the answer for your quiz.
1. Model database is used as a tempelate for new database, so if there are some existing databases, creating user in model won’t help.
2. Even if we could create a user in model database, it won’t get replicated in newly created database.
3. Adding user to model DB also defeates the minimal required security proncipal because business may not like to get their critical DB accessed without knowing about it.
Next time I will certainly check what is running inside my model database.
Thanks,
Anil
Good answers.
No responses? Pinal, what is the downside of adding the user to the model DB? (Provided there are no existing databases the user has to be added to.)
you are right Benjamin!
Cool, thanks.
I love your blog! (Especially the test DDL to clearly demonstrate concepts.)
We use sp_msforeachdb on a daily job that verifies if exists some table without a primary key.
Cool. Be aware of point made by twoknightsthenight.
I don’t trust sp_msforeachdb as I’ve gotten in trouble with it missing DBs. I thought I was crazy and then I found this…
Of course there are other methods for getting around it. But I do need to point out that goofy naming conventions will screw it up.
you are correct twoknightsthenight. I already said its undocumented so one need to be careful using it.