Created a user with permissions to one table, but unable to access the database that contains the table

Question

I've tried to create a Login that will have access to a single table from a few different databases.

When I try and query the table, using the created Login I get the following error:

The server principal "log_Reader" is not able to access the database "MyDB" under the current security context.

Here's the SQL I used to create the Login/User:

USE MASTER
GO

CREATE LOGIN log_Reader
WITH PASSWORD = '<password>'
GO



USE DB1
GO
CREATE USER log_Reader FOR LOGIN log_Reader
GRANT SELECT ON dbo.logtable TO log_Reader

USE DB2
GO
CREATE USER log_Reader FOR LOGIN log_Reader
GRANT SELECT ON dbo.logtable TO log_Reader

USE DB3
GO
CREATE USER log_Reader FOR LOGIN log_Reader
GRANT SELECT ON dbo.logtable TO log_Reader

USE DB4
GO
CREATE USER log_Reader FOR LOGIN log_Reader
GRANT SELECT ON dbo.logtable TO log_Reader

Does anyone have any ideas what I've missed ?

Thanks, Jason

Answer 1

I've got this to work.

It looks like the problem was the initial default schema when creating the User.

I dropped the Login and User and recreated with a default schema and it worked.