mapping database user to database

[kamalteja]

Hi,
Though this is database query, i'm running this as part of my vb script, i've created sqldatabase user using
SQL = "Create Login sample1 WITH PASSWORD = 'Welcome1' , DEFAULT_DATABASE = DEMO "
The database user gets created successfully, however it is not mapped with the database when i verify.

using sql server 2005 sp1, i've verified in sql management studio after database user creation whether the user is mapped or not, and its not.

[KVK]

Hi Teja,

If you are using ADODB connection in Vbscript, refer to the below site to get the correct database string to be used for 2005sp1.

http://connectionstrings.com/

Get the connection string from the above site and use it

Thanks
Vinod

[kamalteja]

Hi,

Yes i'm using ADODB connection and i'm able to establish the connection and create database user name and password. They are visible, however, when i check in SQL Management Studio, the user is not mapped with the database which i used while creating the user

[MVChowdary]

Hi,

See below code, it works fine.

Code:

'    Connect to the flight 32 database
    Set Conn = CreateObject("ADODB.Connection")
    Set Rs = CreateObject("ADODB.recordset")
    'Conn.Open("DSN=Flight32")
    Conn.Open("DSN=QT_Flight32")
    
    'Specify the query
    
    sql = "Select * from Orders"
    
    'Set the recordset cursor type
    Rs.CursorType = 1 'set to a Keyset cursor type
    
    'Execute the Query
    Rs.Open sql,Conn
    
    'Get the no records returned by query
    Recordcount = Rs.RecordCount
    
    'Adding a column to datatable
    DataTable.GlobalSheet.AddParameter "Customer_Name",""
    
    'Get the values from the customer_Name column
    
    While(NOT Rs.EOF)
    
    'Msgbox Rs.Fields("Customer_Name")
    For i=1 to Recordcount
    DataTable.SetCurrentRow(i)
    
    'Writing the data to the datatable
    DataTable("Customer_Name",DtGlobalSheet) = Rs.Fields("Customer_Name")
    
    Rs.MoveNext
    Next
    
    wend
    
    'Exporting the Datatable to C:\DatabaseExample.xls
    DataTable.Export "C:\DatabaseExample.xls"
    
    'close the database connection
    
    Conn.close
    
    Set Conn = Nothing
    Set Rs = Nothing

[kamalteja]

The above statements only tell me how to connect and fetch data from DB, here is my script

Code:

Set Con = CreateObject("ADODB.Connection")  
Set rs = CreateObject("ADODB.Recordset")
Con.Open "Provider=SQLOLEDB;DBDatasource=XXXX;Database=master;Integrated Security = SSPI;DBOracle=false"
Con.Open connectionstring
SQL="Create Database XYZ"  --- the database XYZ gets created successfully
rs.open SQL, Con
Con.Close
Con.Open "Provider=SQLOLEDB;DBDatasource=XYZ;Database=XYZ;Integrated Security = SSPI;DBOracle=false;VNETClassLibrary=Standard"
SQL = "Create Login  <username> WITH PASSWORD = <'password'> , DEFAULT_DATABASE = XYZ "  --- the user gets created for database XYZ, however when verified in SQL Management studio the user is not mapped with the database
rs.open SQL, Con
Con.Close