Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

grant select Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 7:58 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:15 PM
Points: 522, Visits: 2,013
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 ?
Post #1548558
Posted Friday, March 7, 2014 4:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,362, Visits: 15,269
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.
Post #1548671
Posted Friday, March 7, 2014 5:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 6,304, Visits: 13,601
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"
Post #1548689
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse