Cannot find the user '', because it does not exist or you do not have permission

38
votes

I am trying to add permissions to a Store procedure for a user using this query,

USE [MyDatabaseName]
GO

GRANT EXEC ON [dbo].[StoreProcedureName] TO [UserName]
GO

I can give permissions to user through theUser Interface but using this query I get this error,

Cannot find the user 'UserName', because it does not exist or you do not have permission.

If I don't have permissions then how can I add permissions using User Interface.

5
sql-servertsqlsql-server-2005stored-procedures
Are you sure the UserName exists? If it's a windows account prepend the domain name too. – Ulises
I made it up and yeah my user with that name does exists.. – Mathematics
The error message is pretty self-explanatory - are you connected as a sysadmin or dbo? Either the username is incorrect, or you don't have adequate permissions. – Aaron Bertrand
I am running query in SQL Server management studio and if I don't have permissions then how can I add it throw UI ?? – Mathematics
What does the query select * from sys.database_principals where name = 'UserName' return? And is your server using a case-sensitive collation? – Pondlife

5 Answers

50
votes

I just encountered the same problem.

Make sure there is an user mapping for your login in the master table. No database role memberships are required, just tick 'Map' for the database master under 'User Mapping' in the properties for the login 'UserName'.

15
votes

Run this:

USE [db_where_you_need_access]
GO
CREATE USER [your_user] FOR LOGIN [your_user]
4
votes

I had this issue and yet I am in the local admin group on the server. Apparently if UAC is turned onrunning SQL Server Management Studio as administrator makes a world of difference. I didn't have any permissions until I did this.

0
votes

You can get problems like this when a database has been restored from another server and the GUID of the user in the database is different from that of the current server.

This will re-link orphaned users:

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 
   @LoginName='<login_name>';
GO

other than this, if the user exists and you have the relevant security rights, there is no reason what you doing wouldn't work.

0
votes

A bit of a daft one, but make sure your UserName is spelt correctly in the GRANT statement. I spent half an hour trying to figure out what the cause of this error was and it was simply a typo on my part!