Single line to give permissions at the schema level to a role
GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole
And a second line to add users to the role
EXEC sp_addrolemember 'SomeRole', 'whatever user'
And do this in model so all new databases inherit.
Reasons, you should set permissions once only:
- A schema is a container for objects.
- New objects inherit permissions from the schema
- A role is container for users
- New users are added to a role and inherit
As you've found, migrating or restoring a database can lose object permissions when assigned directly to users. So why put yourself in that position?
You can also CREATE LOGIN with a SID so it is the same on all your servers and you don't get orphaned users either.
If you'd asked the correct question we could have saved you some coding...
Personally and finally, I'd say this is bad practice on "need to have", not "blanket do anything"
Idera SQL Permissions Extractor seems to be the product you're looking for.
It can script server and object permissions and it is free. There is also a commercial edition, called SQL Secure which has more features.
The feature comparison between the two editions can be found here.
Best Answer
This should get you what you're looking for:
This will get objects that are owned by your particular user (substitute
'YourUser'
of course). The types of objects this query pulls are: