Querying MS-SQL database from Excel 1

[ColumbiaDiver]

Hi All,

Hoping someone can point me in the right direction in respects to a question I have in querying an MS-SQL database from Excel 2010.

I work for a large company and we don’t have a method in place to track what users have access to what systems, (350 at last count). So what happens when a user transfers or leaves the company is our security department basically SPAMS every system admin in the company with a e-mail that basically says please check all systems you support to see if these users exist in your system and if they do term their account and if they don’t respond that they were not found.

The problem is I am the system admin for multiple systems and each of those systems have multiple environments, (Dev, Test, Prod, Lab, Sandbox etc). Plus we get a lot of term requests on a daily basis. As I’m sure you can imagine checking 5 environments across 5 systems gets pretty time consuming and to make it worse 90-95% of the time those users don’t even exist in the systems I support. So if I get a request to term 10 users across (20 systems/environments) it gets pretty time consuming, log in look for users, log out, log into next system/environment, look for users etc etc.

The systems all have MS-SQL databases that contain tables that list the user profiles and their status. I was going to write a program to do a read query against the database for a list of terms, but unfortunately ran up against our security policies which would require a lengthy approval process to be allowed to deploy such an application so I am limited to using what we already have.

I created an Excel template that I and my other admin populate with the list of requested terms, which makes it easier, but still overly time consuming.

The template has a column for each system and each environment for each system. What I would like to do is:
1) Populate the template with the list of terms, Col1 = User ID, Col2 =User Name, (this is done by copying the information from the e-mail)
2) Execute an MS-SQL against the database\user table from Excel for each UserID to see if it exists in the system. Then doing the same for each system/environment for example,

Select * from database\userTable where UserId = data_in_Cell_A1 (the user ID) and if you find it then place the word FOUND in the Cell A3 – (SystemABC Dev). Then repeat down the rows for cells A2, A3, A4 etc. placing the respective FOUND or NOT FOUND in the column for A4 – (SystemABC Test) A5 – (SystemABC Prod) A6 – (SystemXYZ Dev) etc.

So I see two possible approaches (1 I can query MS-SQL for each userID in each cell of the sheet or (2 I can export the entire user table to a separate Excel sheet each time you open the template to start a new set of terms then execute a lookup against that sheet.

So far I have been able to query the database against a value in the userID cell and if it’s exists return the UserID and the level of access to the adjacent cells (B1, C1), but I can’t figure out how to easily replicate the query for each of the following row cells with the other UserID's in them, query the database against the values in A2, A3, A4, A5 and populate the adjacent cell with FOUND or NOT FOUND and so on.

I’m also concerned about the possibility of executing multiple queries bogging down the system.


Thanks

Gordon

 

[SkipVought]

Good job, Steve!

You also might want to check out
faq68-5829

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]