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]

[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