+ Reply to Thread
Results 1 to 2 of 2

SQL 2008 Loop through objects and set permissions

  1. SQL 2008 Loop through objects and set permissions

    Hi
    I am developing a new SQL 2008 db for a client and they will be using
    it for basic functions while I continue to develop it. I am using
    names for the objects that tell me which users / roles are allowed to
    execute, select, read etc but it can take a lot of time to set the
    permissions - and it is easy to forget to do it. I would like to
    create a stored proc which would loop through each set of objects (ie
    the tables, views and SPs) and set their permissions according to my
    naming rules (eg SPs with names beginning AP - - grant execute to
    role1, read to role2). Can anyone get me started with the code to loop
    through the tables and set permissions?
    Thanks in anticipation, Neil

  2. Re: SQL 2008 Loop through objects and set permissions

    neilr (neilryderatyahoodotcom) writes:
    > I am developing a new SQL 2008 db for a client and they will be using
    > it for basic functions while I continue to develop it. I am using
    > names for the objects that tell me which users / roles are allowed to
    > execute, select, read etc but it can take a lot of time to set the
    > permissions - and it is easy to forget to do it. I would like to
    > create a stored proc which would loop through each set of objects (ie
    > the tables, views and SPs) and set their permissions according to my
    > naming rules (eg SPs with names beginning AP - - grant execute to
    > role1, read to role2). Can anyone get me started with the code to loop
    > through the tables and set permissions?


    In all fairness, this would be a lot easier with some refactoring.
    Rather than using prefixes, use schemas instead. That is, instead
    of

    CREATE PROCEDURE AP_get_order_sp ...

    do

    CREATE PROCEDURE AP.get_order_sp ..

    Once you have set this up, the permission task is trivial, since you
    can grant permissions on schema level:

    GRANT EXECUTE ON SCHEMA::AP TO role1
    GRANT SELECT ON SCHEMA::AP TO role2

    The permissions then apply to all objects of the applicable type in
    the schema, including future objects.

    If you still want to pursue your original idea, here is an example of
    how to do it. You probably want to repackage it a bit to fit your needs.

    DECLARE @proc nvarchar(1024),
    @sql nvarchar(MAX)

    DECLARE cur STATIC LOCAL FOR
    SELECT quotename(s.name) + '.' quotename(o.name)
    FROM sys.objects o
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE o.name like 'AP%'
    AND o.type = 'P'

    OPEN cur

    WHILE 1 = 1
    BEGIN
    FETCH cur INTO @proc
    IF @@fetch_status <> 0
    BREAK
    SELECT @sql= 'GRANT EXECUTE ON ' + @proc TO role1
    EXEC (@sql)
    END

    DEALLOCATE cur


    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx


+ Reply to Thread