Permission to see one table, view or other object

PROBLEM:
You want to allow a user to read only one database table, view or other object

SOLUTION:

Use the following script:

-- replace 'MY_DOMAIN\MyLogin' with your login name
-- you can skip this create login if you have already a domain user
-- alternatively, you can create login using SQL Server authentication
USE [master]
GO
CREATE LOGIN [MY_DOMAIN\MyLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

-- replace 'MyUser' with your user name
-- replace 'MyDatabase' with your database name that contains the table
-- replace 'MyTable' with your table name
USE [MyDatabase]

--create a role to wrap up the permission
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyObjectAccess' AND type = 'R')
BEGIN
       EXEC SP_DROPROLEMEMBER 'MyObjectAccess', 'MyUser'
       DROP ROLE [MyObjectAccess]
END
GO

CREATE ROLE MyObjectAccess
GRANT SELECT ON [MyDatabase].[DBO].[MyTable] TO MyObjectAccess;
GO

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'MyUser')
DROP USER [MyUser]
GO
CREATE USER [MyUser] FOR LOGIN [MyLogin]
GO

EXEC sp_addrolemember N'MyObjectAccess', N'MyUser'
GO

-- Now, login to SQL Server as MyLogin
-- You should see all databases that exist in the instance, but you will not see their objects.
-- You can read MyDatabase.MyTableName

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!