Cannot open database "\'name\': \'master\'" requested by the login-CodePudding

I am currently running into the error

Cannot open database "'name': 'master'" requested by the login. The login failed

when I try and execute my PrintCSV view function in a Django/Python web app

The function is supposed to read through each database on the server and print all the accounts that are in areas.

I am unsure whether this problem might be with my login privileges or with my code itself.

Views.py:

def PrintCSV(request):
    #Getting list of DB Names
    cnxn = pyodbc.connect('DRIVER={SQL Server};'
                          'SERVER=serverIP;'
                          'PORT=port;'
                          'UID=kyle_dev_ro;'
                          'PWD=password;')

    databaseName = 'SELECT name FROM sys.databases'

    cursor = cnxn.cursor();
    cursor.execute(databaseName);
    XdatabaseNames = cursor.fetchall()
    cursor.close()
    dbNames= []
    for row in XdatabaseNames:
        rdict = {}
        rdict["name"] = row[0]
        dbNames.append(rdict)

    #Starting CSV
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename="Avonlea_file.csv"'
    writer = csv.writer(response)
    writer.writerow(['Unit', 'Balance'
                     ])

    for x in dbNames:
        connect = pyodbc.connect('DRIVER={SQL Server};'
                                 'SERVER=192.168.1.1;'
                                 'PORT=1433;'
                                 'Database='   str(x)   ';'
                                 'UID=kyle_dev_ro;'
                                 'PWD=fh$sa#8d#7F8Y3;')

        XaccAreas = " SELECT Account, DCBalance FROM [dbo].[Client] where iAgeingTermID = '3' AND DCBalance < 0 "
        cursor = cnxn.cursor();
        cursor.execute(XaccAreas);
        accAreas = cursor.fetchall()
        cursor.close()
        for z in accAreas:
            rdict = {}
            rdict["Account"] = z[0]
            rdict["Balance"] = z[1]
            writer.writerow([
                '',
                x,
                '',
            ])
            writer.writerow([
                rdict[0],
                rdict[1],
            ])

    return response

Full Error:

('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "'name': 'master'" requested by the login. The login failed. (4060) (SQLDriverConnect); [42000] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "'name': 'master'" requested by the login. The login failed. (4060); [42000] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')

When I had set up the login on Microsoft SQL Studio I had ensured that all the "Mapping" databases that were needed to be ticked, were ticked and that my default database was set to "master"

So I am unsure whether this error originates from the privileges I have given this login?

CodePudding user response：

You have made two mistakes:

'Database=' str(x) ';' should be 'Database=' str(x["name"]) ';' because you need to get a particular column, not the whole row
You probably want to filter out system databases from your query anyway

select name
from sys.databases
where database_id > 4