setting default permissions for new objects

Question

setting default permissions for new objects

Paul Davies-315732

SSC Journeyman

Points: 84
More actions
February 22, 2007 at 12:30 pm

#172394

I suspect I am missing something basic here, but is there a way to set default or global permissions for new objects, e.g. views?
I create views for people to be used with Excel data queries, but always forget to give the user group the select permission!
TIA
Paul

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mikedotnet SSC Enthusiast Points: 104 More actions · Answer 1

I'm not sure about default or global permissions (or if you would really want to do that), but what I do is use a standard template where at the end of the sproc/view Create template I have a "GRANT EXECUTE ON <sprocname> TO <user/role>" or the same sort of thing in the view template.

This way when I'm done writing the SQL everything is taken care of in one execute and it also gives me a simple way to track who has access to the sproc/view and if you ever have to recreate or move the view or sproc you can simply run the create script. When another user/role needs access I just add another GRANT statement to the create script and give access that way.

Maybe there's a better way, but this has worked for me.

Paul Davies-315732 SSC Journeyman Points: 84 More actions · Answer 2

Mike

that seems a good idea - I'm very much a newbie at the structural element of SQL Server

I'll give it a go

Thanks

Paul

steve block SSCertifiable Points: 7698 More actions · Answer 3

I would set up a role that you assign "everyone" to (or at least a large group of users). When you create a table or view, be sure to grant select permission to it for the role.

Steve