Granting Object Permissions on Tables

Question

Granting Object Permissions on Tables

Jacob Pressures

SSCertifiable

Points: 5804
More actions
September 29, 2005 at 2:28 pm

#91337

I have not created all my stored procedures yet, so i'm still using queries.
I want to grant my approle object permissions
grant select, insert, update, references on employees to faelogin
I have 15 tables! Do i have to list all 15? There isn't a word called ALL!

Viewing 4 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

only 15? I've got 978

Add the roles db_datareader and db_datawriter to the login faelogin. That will automatically grant them rights to read and write all tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 2

Try something like this...

SELECT 'GRANT SELECT, INSERT, UPDATE ON ' + name + ' TO faelogin'

FROM sysobjects

WHERE type = 'U' and status >= 0

Then copy the resulting script and run it.

Gary Johnson
Sr Database Engineer

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 3

Thanks! That is good info from both of you. I think it might be best for me to learn how to add the permissions from one role to anohter and i don't know how.