March 6, 2014 at 7:58 pm
i have 100 + of tables want to provide select permissions to all the tables which starts with A in a database with dbo is schema , how can i provide ?
March 7, 2014 at 4:35 am
ramyours2003 (3/6/2014)
i have 100 + of tables want to provide select permissions to all the tables which starts with A in a database with dbo is schema , how can i provide ?
Write a query using INFORMATION_SCHEMA.TABLES and create the Dynamic SQL string and copy and paste into another window.
It would take me 30 seconds to write it myself.
Putting the output into a variable and using sp_executesql would automate it.
March 7, 2014 at 5:26 am
ramyours2003 (3/6/2014)
i have 100 + of tables want to provide select permissions to all the tables which starts with A in a database with dbo is schema , how can i provide ?
Overkill would to provide SELECT on the dbo schema or assign db_datareader permission.
You could write a query to pick up all table names and generate the grant statement.
However, why not just create a database role with grant on all existing required tables and when the user adds a new table, add it to the role.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply