Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Aug 11 '13 at 6:57

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.