0

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.

enter image description here

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?

4

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
2
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.

3
  • Well, I did differentiate between users and logins when I said I could add a login for the guest account. Anyway, guess I was looking more for a step-by-step. I'm an experienced developer, but not a database administrator. Doesn't SSMS support this? Aug 24 at 18:00
  • You may find the documentation helpful if you are looking for step by step instructions on how to add a user to the aforementioned database role (assuming you are clear on setting up login-user mapping). Aug 24 at 18:10
  • Guest is a user, not a login. So the concept of "adding a login for the guest account" doesn't make sense - you got it the other way around (no, you can't connect a login to guest user). Unless you mean the guest account in Windows of course, which would be a weird twist - but I doubt that is what you meant. Yes, SSMS supports creating a login, a user and granting db_datareader to this user. No problems. SSMS doesn't support enabling guest without using the query window, but again, this isn't what you are looking for. Aug 24 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.