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
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?
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
You can use the built-in database role
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>'
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'
SELECT permissions on the whole database use this:
USE <MY_DATABASE> GRANT SELECT ON DATABASE :: <MY_DATABASE> TO <MY_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