Quick script to grant read only and view definition on all databases

This is a handy little script to use for development databases, when you want all members of a certain domain group to be able to see all data in all databases, and view all definitions.  Feel free to modify as desired!

EXEC sp_MSForEachDb '
USE ?
IF NOT EXISTS (SELECT * FROM DBO.SYSUSERS WHERE NAME = ''sea\DevUsers'' ) CREATE USER [sea\DevUsers] FROM LOGIN [sea\DevUsers]  

EXEC sp_addrolemember db_datareader, [sea\DevUsers]
GRANT VIEW DEFINITION TO [sea\DevUsers]
'

Wildcard pattern matching with metadata table

We had a situation recently where test records, created by processes upstream of our data warehouse, were causing issues with our data. Since the test records were created by many different groups, their names didn't follow any pattern, so they were not easy to exclude. We ended up having where clauses like this in many different stored procedures:

Where
    StoreName not like '% test st%'
    and StoreName not like '% test 2%'

Keeping these updated across all the stored procedures was a hassle, so test records were continually getting into our data and causing problems. I decided to create one metadata table for all the test patterns, and then create a view to join to that table. The view would be used to exclude all the test records whenever necessary. First, let's create the sample data:

Create Table TestStoreNameWildcard(WildcardPattern varchar(50))
Insert into TestStoreNameWildcard 
values 
    ('% test st%')
    ,('% test 2%')
    ,('% test 3%')
    ,('%test region%')

Create table Store (StoreID int, StoreName varchar(50))
Insert into Store values 
    (1, 'Corner Store')
    ,(2, 'ABC Store')
    ,(3, 'Region A Test Store')
    ,(4, 'Test Region 1 Store')
    ,(5, 'Region 5 Test 2 Store')
    ,(6, 'Target')
    ,(7, 'Contest store')
    ,(8, 'Ann''s Book Store')

Now create a view that returns only those stores which fit our pattern, joining with "like".

Create view vTestStore as
Select
    StoreID
    ,StoreName
from Store
    join TestStoreNameWildcard    
        on Store.StoreName like TestStoreNameWildcard.WildcardPattern

Now, when you run this select:

Select * from vTestStore    

You'll see only those stores that we've identified as test stores. The view vTestStore can be used to exclude test records wherever needed. If performance is a problem, it could be created as a table or indexed view.

StoreID     StoreName
----------- --------------------------------------------------
3           Region A Test Store
5           Region 5 Test 2 Store
4           Test Region 1 Store