patternsqlModerate

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

simplereaduserwaythereonlyadd

Problem

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?

Solution

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 = ''
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

Code Snippets

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

Context

StackExchange Database Administrators Q#298493, answer score: 13

Revisions (0)

No revisions yet.