I have a database Mydatabase with a view in it, MyView.
I want any user who is a member of the Public role to be able to select from the view.
I've done GRANT SELECT ON MyView TO [Public], created a test login on the sql server called Test and made it a member of the Public role, but still can't select the View.
What am I doing wrong?
A Login and a user are different in nature. A login is at the server level, and a user is at a database level. Every user has a corresponding login. At the same time, not every login has a corresponding user account.
USE MyDB
GO
CREATE USER Test FROM LOGIN Test
GO
GRANT CONNECT TO Test
GO
The login "Test" needs set up in the database as a "User". By default, all users are a member of the database public role.
If you want to give everyone or Public connect access to a database, go into the databases properties, goto the permissions page, add "guest" to the "users or roles" list, then select it, and in the lower half, find the "Connect" row, select "Grant", tuning it on, then click OK. This gives all Server Logins connect accss to that database.