17

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?

0
23

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
0
4

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

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.

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.