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?
Best Answer
You are going to have to use dynamic SQL. Even if there were a way to access a system table that has all the information on on server, you would need dynamic SQL for the four-part naming on other servers.
By the way, you can resolve variables inside queries. However, a variable does not work for a server, database, table, or column name. In short, variables replace constants.
Here is a suggestion on how to do it in your case.