Cannot Find the User Because It Does Not Exist in SQL Server

In this SQL Server tutorial, you will learn how to resolve the error cannot find the user because it does not exist or you do not have permission.

You will understand the two reasons which are responsible for that error. Then, you will learn how to solve that error using the query and SQL Server Management Studio.

Finally, you will also learn about some common mistakes that you should not follow if you don’t want to get that error.

Cannot Find the User Because It Does Not Exist in SQL Server

Error message ‘Cannot find the user because it does not exist or you do not have permission’ occurs due to two reasons.

• The first reason is that it cannot locate the user to whom you are referring, or the user doesn’t exist in your database or SQL Server.
• Second is the permission, whatever operation you want to perform, for that user doesn’t have valid permission.

Before solving the error, let me show you how this kind of error can occur using the examples.

Open the command prompt and connect to the SQL Server instance using the SQLCMD.

Type the below query to view all the databases on the SQL Server.

SELECT name FROM sys.databases;

When you execute the above query, it shows all the databases on the SQL Server; now, here, I will choose the database ‘E_commerce’ (in your case, it can be any database) that contains the stored procedure that I have already created.

So, I will show you how to grant execute permission on stored procedures for a specific user, and if the user exists and has valid permission, then the execute permission is granted otherwise, it raises the error.

Next, view all the login users on the SQL Server using the below query.

SELECT name FROM sys.sql_logins;
GO

As you can see, all the login users on the SQL Server instance look at them carefully and note them down.

Choose the E_commerce database using the below query.

USE E_commerce;
GO

After choosing the database, view all the stored procedures within the current database using the below query.

SELECT name FROM sys.procedures;
GO

Suppose you want to grant a execute permission on the UpdateTableData stored procedure to a user ‘Lucas’. As you know, user ‘Lucas’ doesn’t exist in your SQL Server, but to understand the error, let’s run the below query.

GRANT EXECUTE ON dbo.UpdateTableData TO [Lucas];
GO

As soon as you execute the query, it shows the error ‘Cannot find the user ‘Lucas’, because it does not exist or you do not have permission.’ that you can see in the above output.

Now, this error is because the user ‘Lucas’ doesn’t exist, which is indeed true. So, to resolve this error, create the login user named ‘Lucas’ using the below steps.

Again, open the new command prompt and connect to the SQL Server instance using the SQLCMD.

And execute the below query to create a user named ‘Lucas’.

CREATE LOGIN [Lucas] WITH PASSWORD = '456798';
GO

View the created user using the below query.

SELECT name FROM sys.sql_logins;
GO

You have successfully created the ‘Lucas’ user; now, again, choose the database (E_commerce) and execute the below query granting an execute permission on the UpdateTableData stored procedure to a user ‘Lucas’.

GRANT EXECUTE ON dbo.UpdateTableData TO [Lucas];
GO

You again get the same error, but this time, you have the ‘Lucas’ user on your SQL Server. The reason you get this error is that the ‘Lucas’ user is not mapped to the ‘E_commerce’ database.

So whenever you want to give any kind of permission on any objects in SQL Server to any user, first make sure that the user has access or permission to do that on the specific database or objects. Also, a user should exist in your database.

In this case, you need to map the ‘Lucas’ user to ‘E_commerce’ database. For that, use the below query.

CREATE USER [Lucas] FOR LOGIN [Lucas];
GO

Now, again, execute the below query to grant execute permission.

GRANT EXECUTE ON dbo.UpdateTableData TO [Lucas];
GO

This time, the query was executed without any error, which means you have successfully granted the execute permission on the UpdateTableDate stored procedure to the ‘Lucas’ user.

You can also map the user to a specific database using the SQL Server Management Studio.

For that, open the SSMS, and from the Object Explorer, expand the Security node and then expand the Logins folder. Choose the ‘Lucas’ user, right-click on it, and select the option Properties.

After that, the Login Properties window appears, where select the ‘User Mapping’ from the Select a page section on the left side of the window.

Then tick the check box for the E_commerce database or the database to which you want to map the user. Click on the OK button as shown in the below picture.

After that, you can run the query to grant execute permission on the stored procedure as you have done above.

This is how you can resolve the error ‘Cannot find the user because it does not exist or you do not have permission’ in SQL Server.

But there can be common mistakes that lead to this kind of error, and you should be aware of these mistakes.

• Make sure that there should not be any typographical errors in the username.
• Another mistake is when you create a user at the server level or for login; you think that the same user is also created for all the databases. But in reality, you need to create or map that user for a specific database.
• Always verify the existence of the user before mentioning the user with any query
• When you specify the username with the query, always warp the username with square brackets. For example, [Lucas].

Conclusion

In this SQL Server tutorial, you learned how to solve the error cannot find the user because it does not exist or you do not have permission. You solved this error using the query and SQL Server Management Studio. Also learned about some mistakes that you should not make not to get the error.

You may also like:

• How to Format Numbers with Commas and Decimals in SQL Server?
• How to Create a Temporary Table in SQL Server Management Studio?

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.