Saturday, August 25, 2007

Massively GRANT permissions

The below little SQL Server 2005 script will generate GRANT T-Sql statements for all objects in the database. Fun stuff. There has to be a way to do this through the administration tools quickly... But, until I find it.

SELECT
CASE
WHEN type IN ('U','TF','FN','IF') THEN 'GRANT ALL ON ' + name + ' TO PUBLIC'
WHEN type IN ('P') THEN 'GRANT EXEC ON ' + name + ' TO PUBLIC'
END sql
FROM sys.all_objects WHERE schema_id = [change me] AND type IN ('U','TF','IF','FN','P')

Need to make sure you change the 'schema_id' to the schema_id of the database you want to run the script on. Oh, and the types are probably not complete, so BEWARE. All you have to do is then take the output and run it. Fun, fun and no hard type etc.

The script would also be handy for generating other broad categories of T-Sql for any objects in the database. Nothing though any DBA couldn't do in 5 minutes.

DISCLAIMER: Blah, blah, blah this is for development purposes only and you should do better than just granting ALL access to the PUBLIC. Done.

No comments:

There was an error in this gadget