Permissions Tool

• jpomfret7

  SSCommitted

  Points: 1628

  More actions

  February 13, 2013 at 7:24 am

  #268499

  Good Morning all,

  I'm looking for a tool that would allow me to group objects together and assign permissions based on those groups. Such as Joe gets rights to all tables/functions/procs that start with ABC instead of all objects in a database.

  I realize using schemas would probably be a good option, but with the existing code base this isn't possible at the moment.

  Anyone have any ideas?

  Thanks in advance

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 13, 2013 at 7:35 am

  #1587456

  jpomfret7 (2/13/2013)

  ---

  Good Morning all,

  I'm looking for a tool that would allow me to group objects together and assign permissions based on those groups. Such as Joe gets rights to all tables/functions/procs that start with ABC instead of all objects in a database.

  I realize using schemas would probably be a good option, but with the existing code base this isn't possible at the moment.

  Anyone have any ideas?

  Thanks in advance

  I don't know of a tool, per se.

  Typically, i would greate a Role , ie "CREATE ROLE RoleAbcUpdates",

  Then use the metadata to create teh scripts that assign the desired permissions to that role:

  ie:

  declare @permissions varchar(max)

  SELECT @permissions = 'GRANT SELECT,UPDATE,INSERT ON ' + name + ' TO AbcUpdates ;'

  from sys.tables

  WHERE LEFT(name,3) = 'ABC';

  EXEC(@permissions);

  then i would add Joe to that and other roles.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• jpomfret7

  SSCommitted

  Points: 1628

  More actions

  February 13, 2013 at 8:30 am

  #1587504

  I hadn't thought of roles, thanks for the suggestion.