Granting Select access to all tables within a specific Database in MS SQL

Question

I have a server that has multiple databases that are contained within that same server/project. I am using MS SQL Server 2012.

I have a special role set up with 3 users that are apart of that role. I would like to grant Select access to the role for all tables that are within 1 specific database.

The database in question is an archive database that has archive tables for each month for the past 12 years. When originally creating the role, I granted access by just running the following:

GRANT SELECT ON [dbo].[myarchivetable] TO myspecialrole

go

This worked great because I would just have to change the year and month and run it all as 1 script.

I am now finding out that they are needing Select access to every table in the archive database. I could do the above method, however Having to write all of that out 132 times, it would be very easy to make a mistake.

How can I easily add the myspecialrole to every table within that database?

Answer 1

If all your tables are in the dbo schema then this should work, you can just deny single tables if required

GRANT SELECT ON SCHEMA :: [dbo] TO myspecialrole

Answer 2

You can use the built-in database role db_datareader

EXEC sp_addrolemember N'db_datareader', N'<MYUSER>'

or you can create your own database role

USE <MYDB>
CREATE ROLE [<MYROLE>]
GRANT SELECT ON SCHEMA::[dbo] TO <MYROLE>
EXEC sp_addrolemember N'<MYROLE>', N'<MYUSER>'

Answer 3

You can generate and run a sql with sysobjects system table as follows:

select 'GRANT EXEC ON dbo.' + name + ' TO myspecialrole GO' from mydb..sysobjects where type = 'U'

Answer 4

To grant SELECT permissions on the whole database use this:

USE <MY_DATABASE>
GRANT SELECT ON DATABASE :: <MY_DATABASE> TO <MY_USER>

where

• <MY_USER> is user
• <MY_DATABASE> is database name

Granting permissions on schema doesn't help, simple reason: if new schemas occur the user will not have permissions.

Granting permissions on all objects doesn't for the same reason, the user will not have permissions on new objects created after GRANT event.

Answer 5

Create AD groups to easily manage access to database. You can then add/ remove individuals to these groups for controlled access

CREATE USER [mydb-admin-group] FROM EXTERNAL PROVIDER
GRANT SELECT ON SCHEMA :: dbo TO [mydb-admin-group]

If there are multiple schema, run the permissions for each