User does not have permission to access a database

Question

I'm trying to connect to a database using Windows Authentication. I believe that my current user does not have access to it.

How can I enable a user to login to SQL Server, and use the database?

Answer 1

You need to use the SQL Server Management Studio program to grant access for the user. You'll need to connect in with a login that has administration privileges for the database. If you have don't have those privileges you'll need to contact someone that does.

If you do have a login with those privileges:

• open Management Studio
• connect to the database server the database is on and look for the Security node in Object Explorer.
• Expand the Security node and look for the name of the user in the list of Logins. The user's name should be the same as the user's Windows login if you are using Windows Authentication DOMAIN\Username format.
• If the user is there, you will need to grant that user appropriate permissions to the database (read, execute SPs, etc.).
• If the user isn't there you will need to add them.

Permission can also be added by group so you should check for groups that the user belongs to as well.

Answer 2

I had a scenario where I inherited a PC from another developer that left the organization. I couldn't access the default instance using Windows Authentication.

Here was the solution:

1. Open up SQL Server Configuration Manager
2. Click on "SQL Server Services"
3. Locate the Instance in the right pane and double-click for its properties
4. In the "Log On" tab, notice the "Log on as:" radio button option is set to "Built-in account".
5. Change the option to "This account" and add your Windows Authentication account with your domain and username and enter your password.
6. Click "Apply". Click "Yes" when it asks you if you want to restart the instance.

This will automatically add your Windows Authentication user account (Active Directory or local user) to the SQL Server instance. You will now be able to connect right away to the selected instance. As best practice, reset the settings back to the Built-in user account (most likely Network Service).

That's it!