Is there a simple way to add a read-only user?

Question

I have a hosted (shared) SQL Server database and I'd like to add a read-only login. (This login will be used to build Power BI data visualization.)

Looking into this, it's far more complex than I expected.

Ideally, the guest user would be read-only and I could just add a login for the guest account. But it appears this account does not have login permission initially.

Could someone offer the simplest way--the way that alters the database the least-- to add a login that has read-only access?

In addition, is there a way to prevent access to some tables, such as my user membership tables?

Answer 1

Could someone offer the simplest way--the way that alters the database the least-- to add a login that has read-only access?

create login pbi_reader with password = '<your strong password>'
create user pbi_reader for login pbi_reader 

--some combination of:
grant select to pbi_reader  --grant select on whole database
grant select on schema::dbo to pbi_reader  --grant select on one schema
grant select on dbo.some_table to pbi_reader  --grant select on one table database
deny select on dbo.user_membership to pbi_reader --override the grant with a deny for one table

Answer 2

You need to differentiate between a user and a login. The login is used to login to your instance. This login then need a user in each database that this login want to access.

Then you quite simply make that user a member of the db_datareader role in the database.

The guest user isn't what you are looking for. If you enable guest (GRANT CONNECT TO guest), then all logins can now access the database with whatever permissions you assign to the guest user. The guest user don't have any privileges by default, it is for you to assign. But again, the guest user isn't what you are looking for.

If you have exceptions, you can DENY SELECT ON tablename TO username for these table exceptions. DENY takes precedence over GRANT.

There are some aspects and exceptions to above, but I don't want to muddle the waters and dig into explanations what aren't relevant to the case at hand, hence my slight generalizations and simplifications above.