(c) 2002 Visual Studio Magazine 
Fawcette Technical Publications

Issue
May VSM 2002
Section
Features
Listing file name
Vs0205MBl1.rtf
Status
At AR
Character count
2,160


Listing 1								VBScript and html
Secure Data Retrieval with no Messy UID and Password
Instead of hard coding a user id and password in your script or prompting the user for a password, simply authenticate the user using a trusted connection.  Then you can get on to the fun stuff - getting the data with ADO.  Additionally, you now have fine-grained security, a database administrator that likes you and a happy user.

<%@ Language=VBScript %>

<% 
' Note that the user that calls this page must be
' a member of the sysadmin or securityadmin fixed
' server roles in SQL Server 2000.
Function GetRecordset()
	Dim oConn
	
	Set oConn = CreateObject("ADODB.Connection")
	
	' Open connection using trusted connection.
	oConn.Open "Provider=SQLOLEDB;" & _
		"Initial Catalog=PUBS;" & _
		"Data Source=localhost;" & _
		"Trusted_Connection=yes"
	
	' Make call to sp_helplogins system procedure 
	' to get information on supplied login.

	Set GetRecordset = oConn.Execute("EXEC " & _
		"sp_helplogins '" & _
		Request.Form("Username") & "'")
End Function%>   

<html>
<head>
<title>SQL Server 2000 Trusted Connection</title>
<link rel="stylesheet" type="text/css" href="default.css">
</head>
<body>
<h1>SQL Server 2000 User Login Information</h1>

<table cellpadding="3" cellspacing="0" border="1">
	<tr>
		<th>LoginName</th>
		<th>Default Database Name</th>
		<th>Default Language Name</th>
	</tr>
	<%
		Dim oRS
		Set oRS = GetRecordset()
		
		While Not oRS.EOF
	%>
			<tr>
				<td><%=oRS("LoginName")%></td>
				<td><%=oRS("DefDBName")%></td>
				<td><%=oRS("DefLangName")%></td>
			</tr>
	<%		
			oRS.MoveNext 
		Wend

		' This query returns two recordsets: 
		' information about the user, and then
		' information about the user logins.
		Dim oRS2
		Set oRS2 = oRS.NextRecordset
	%>
</table>
<br>
<table cellpadding="3" cellspacing="0" border="1" ID="Table1">
	<tr>
		<th>LoginName</th>
		<th>DBName</th>
		<th>UserName</th>
		<th>UserOrAlias</th>
	</tr>
	<%  
		While Not oRS2.EOF 
	%>
			<tr>
				<td><%=oRS2("LoginName")%></td>
				<td><%=oRS2("DBName")%></td>
				<td><%=oRS2("UserName")%></td>
				<td><%=oRS2("UserOrAlias")%></td>	
			</tr>
	<%		
			oRS2.MoveNext 
		Wend
		
		oRS2.Close
		
		Set oRS = Nothing
		Set oRS2 = Nothing
		Set oConn = Nothing
	%>
</table>

</body>
</html>
