I have a task - to make a universal import of data from Excel to sql database, using vba tools.

The main problem is that any users could download data regardless of their access rights to the databases. There are about 10 storages, but each of them has a xx \ Loader domain user, who has write rights to the tables.

The idea is that when connecting to the server, use the login login xx \ Loader, but I can’t find an option in the connection string that allows this. Please tell me how to do it. Alternative solutions will also be arranged. Thank!

    2 answers 2

    Add the SSPI value for the Integrated Security property to your connection string. In this case, the current Windows account privileges are used to authenticate the user.

    If authentication is required for a specific user, then Integrated Security should be set to false . It is also necessary to specify the values ​​for User ID and Password that are required for login.

    Example :

      "PROVIDER=SQLOLEDB.1;PASSWORD=12345;PERSIST SECURITY INFO=TRUE;USER ID=username;INITIAL CATALOG=DBName;DATA SOURCE=Server;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;"

    You can read the documentation or a good tutorial .

    • The fact of the matter is that I do not need authorization under my account. I need authorization under a specific account - xx \ Loader. - T.Zagidullin September
    • @ T.Zagidullin, updated the answer - Anatol
    • If we are talking about authentication under a specific Windows user, then Integrated Security = false will not help. I found the answer here dba.stackexchange.com/questions/66014/… . In short, it is impossible. Alternative to run as another user with / runas - T.Zagidullin
    • I gave a working example Connection String for VBA and SQL Server. What does the screw user have in general, if the server already has the necessary account? - Anatol

    If I understood the question correctly:

    Use the connection in the macro:

     Dim conn As ADODB.Connection Dim cmd As ADODB.Command Set conn = New ADODB.Connection Set cmd = New ADODB.Command login = ... pass = ... conn.Open "Provider=OraOLEDB.Oracle.1;Password=" & pass & ";Persist Security Info=True;User ID=" & login & ";Data Source=..."

    To execute the query, use:

     conn.BeginTrans cmd.ActiveConnection = conn ... createTable = "create table test (id integer, name varchar(256))" cmd.CommandText = createTable cmd.Execute

    Do not forget to add "OLE Automation" in Tools -> References in the macro window.