I need to check for the existence of certain users on some database servers, each of which has over 1,000 databases, and create the users if they don’t exist and assign proper database roles to them. I already do this currently by using loops (no cursor though!) and dynamic SQL, but I was wondering if there is any way to do it by using queries. I’m relatively new to writing T-SQL and – to me – dynamic SQL seems ugly. It seems to me that if there were any way to resolve a variable inside of a query, it would make things easier. For example, I could use the following query as the inner query of a correlated query where the outer query got a list of databases from master.sys.databases:
select name from @dbname.sys.database_principals where type = ‘G’ and name like ‘%SomeUser%’
This doesn't work... but is there any way to do something like this without dynamic SQL?