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.
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'.
Run this:
USE [db_where_you_need_access]
GO
CREATE USER [your_user] FOR LOGIN [your_user]
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.
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.