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

• mikedotnet

  SSC Enthusiast

  Points: 104

  More actions

  February 23, 2007 at 11:55 am

  #691515

  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

  February 23, 2007 at 12:59 pm

  #691533

  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

  March 13, 2007 at 10:28 am

  #694738

  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