CODEWITHSUNDEEP
sqlsql-serverselectpermissions
Amirali Samiei
Amirali Samiei

Reputation: 405

How to assign select permission for all tables?

I want to assign select permission to a user, for all tables, and I need this permission work for every table which will be added to database.

I can assign select permission to my user (data_reader) for all tables which are currently in database, by executing my code result:

select 'grant select on object::'+s.name+'.'+t.name+' to data_reader'
from sys.schemas s
inner join sys.tables t on s.schema_id=t.schema_id

my problem is, if any table added to database I have to execute grant select on that table for data_reader, so it may be forgotten.

I searched a lot, but I couldn't find a way to grant select to all tables in all schemas to my user, forever.

Upvotes: 0

Views: 14308

Answers (3)

Evgeniy Gribkov
Evgeniy Gribkov

Reputation: 515

It is enough to give the rights db_datareader in the mapping to the desired database:

USE [DBName]
GO
ALTER ROLE [db_datareader] ADD MEMBER [user_name]
GO

Upvotes: 1

Roxana Sh
Roxana Sh

Reputation: 292

If all your tables are in the dbo schema then this would be :

GRANT SELECT ON SCHEMA :: [dbo] TO specialrole

and try it :

GRANT {  { schemaPrivileges        | SELECT [ PRIVILEGES ] } ON { SCHEMA <schema_name> | ALL SCHEMAS IN DATABASE <db_name> }
   | { schemaObjectPrivileges  | SELECT [ PRIVILEGES ] } ON FUTURE <object_type>S IN SCHEMA <schema_name> }
TO [ ROLE ] <role_name> [ WITH GRANT OPTION ]

Upvotes: 2

Mahdi Rahimi
Mahdi Rahimi

Reputation: 574

I check this problem on SQL Server 2017

I create a login with Stack Name and map to Data Base User without any permission

Then i grant Select permission to user

grant select to Stack;

Upvotes: -1

Related Questions