Is there a way to find out a list of the SQL Servers that a windows authenticated ASP.NET user on an intranet site has access to?
I'd like to list them in a dropdown.
+1
A:
There are a few ways, to be sure. One that I'm familiar with is the EnumAvailableSQLServers SMO method (have a look here: http://www.sqldbatips.com/showarticle.asp?ID=45)
The trick is verifying that they actually have access to a given server. First, you need to define "has access". Is it whether or not they have a valid SQL login? Whether or not they can access a certain database or securable? You'll need to run through the servers returned by SMO in order to validate each one, based upon whatever criteria you define.
Aaron Alton
2009-08-11 20:08:41
Yeah, you'll either have to attempt a connect as that user and see if it works (time consuming!), or you'll need to log in with a sysadmin account and check some system tables / views to check whether or not that user can connect to that server / any database on that server. Both pretty major undertakings......
marc_s
2009-08-11 20:23:26