Xerxes Xerxes - 1 month ago 21
SQL Question

SQL Server Script to create grant scripts for user roles

I am looking for a script that I can run to create grant permissions for specific user roles in SQL Server, for example given a user role, the following would be generated for all tables and procedures in a database, so that it can be saved and reused later on:

GRANT SELECT ON [dbo].[ResourceSource] TO ApplicationResourcesUpdates;
GRANT INSERT ON [dbo].[ResourceSource] TO ApplicationResourcesUpdates;
GRANT SELECT ON [dbo].[ResourceSource] TO ApplicationResourcesUpdates;
GRANT UPDATE ON [dbo].[ResourceSource] TO ApplicationResourcesUpdates;
GRANT DELETE ON [dbo].[ResourceSource] TO ApplicationResourcesUpdates;
GRANT REFERENCES ON [dbo].[ResourceSource] TO ApplicationResourcesUpdates;

GRANT EXECUTE ON dbo.TruncateAllTables TO ApplicationResourcesUpdates;
GRANT EXECUTE ON import.CountryMerge TO ApplicationResourcesUpdates;
GRANT EXECUTE ON import.CreateAllForeignKeys TO ApplicationResourcesUpdates;
GRANT EXECUTE ON import.CreateAllIndexes TO ApplicationResourcesUpdates;

Answer

Here are a couple of scripts that will generate the SQL for you.

SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON [' + B.name + '].[' + A.name + '] TO [ApplicationResourcesUpdates]'
FROM sys.objects A JOIN sys.schemas B
ON A.schema_id = B.schema_id
WHERE A.type = 'U'

SELECT 'GRANT EXECUTE ON [' + B.name + '].[' + A.name + '] TO [ApplicationResourcesUpdates]'
FROM sys.objects A JOIN sys.schemas B
ON A.schema_id = B.schema_id
WHERE A.type = 'P'

You would just need to execute the output using SSMS. Change the use/group to a variable if you plan to run this for more than one. Simple, but it works. Good for a once in a while type of situation.