Permissions to 100+ tables

Question

Post reply

Permissions to 100+ tables

VastSQL

SSC-Dedicated

Points: 36097
More actions
April 28, 2010 at 1:36 am

#220082

Hi Experts,
which is the best way to give user permission to 100+ tables?
Is it by creating roles and adding the same to particular tables
or
Adding user to each tables
or
IS THERE ANY OTHER BETTER WAY TO ACHIEVE THE SAME?
CAN WE GIVE PERMISSIONS TO MULTIPLE TABLES IN A GO?
Thanks in Advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

to give permissions to all tables and views use the roles

DB_Datareader

DB_Datawriter

or just create scripts to grant the permissions to the tables (remove un needed permissions as required)

GRANT SELECT, INSERT, DELETE, UPDATE ON OBJECT::dbo.mytable1 TO user_or_role

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

The best way is to grant the permissions to a user role. Add the user or group to that role.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 3

VastSQL

SSC-Dedicated

Points: 36097

April 28, 2010 at 10:28 pm

#1159408

Thanks Everyone

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

CirquedeSQLeil (4/28/2010)
The best way is to grant the permissions to a user role. Add the user or group to that role.

TO user_or_role

😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Ratheesh.K.Nair (4/28/2010)
Thanks Everyone

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

MannySingh SSChampion Points: 12465 More actions · Answer 6

you can even do something like this:

SELECT 'GRANT SELECT ON '+quotename(table_name)+' to '+'Maninder' FROM Information_Schema.Tables

WHERE Table_Type='BASE TABLE'

Maninder
www.dbanation.com

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Maninder S. (4/30/2010)
you can even do something like this:
SELECT 'GRANT SELECT ON '+quotename(table_name)+' to '+'Maninder' FROM Information_Schema.Tables
WHERE Table_Type='BASE TABLE'

don't forget to include the schema and use

SELECT'GRANT SELECT ON [' +

s.name + '].[' +

t.name + '] to ' + 'Maninder'

FROM sys.tables t inner join sys.schemas s

on t.schema_id = s.schema_id

WHERE t.type = 'U'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steven James Gray Mr or Mrs. 500 Points: 501 More actions · Answer 8

An alternative syntax to achieve the same for all tables and views in a schema would be:

GRANT SELECT ON SCHEMA:DBO TO user_or_role_namehere;