I've restored a remote database to my local, and it contains some users which are used to execute jobs.
We are trying to execute the job and it is seems to return a connection error (connection to the same database).
It is using a proxy user to run CMD EXEC commands, and we've checked the final user behind the proxy, turns out it seems that this user is not allowed to login to the SQL Server.
We've entered with SA account and taken ownership of the database, however, we are unable to change the user setting.
Please advise.
Best Answer
The screenshot you've posted is just showing that it's a user which uses Windows authentication to log on to SQL Server, though this doesn't have any bearing on whether or not the user can actually access a database.
If you go to the Server Roles tab you can assign a fixed server role which will allow various sets of permissions to the instance, or if you go to User Mapping, you can specify a role for that user against a particular database, or under securables you also have more granular permissions if you only want to assign very specific permissions.
I would ensure that your user has the required permissions mapped against the database you are trying to access. If you're still seeing an error after that, post the specific error message.
Also, just to more specifically answer your question - it's not possible to switch the user to use SQL Server authentication; in this case you would just create a new user which uses SQL authentication, as it is specified at the time the user is created.