Prev: event id 4879 MSDTC Client 2
Next: A way to set developer edition to behave like another edition
From: neilr on 6 Jun 2010 11:24
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
From: Erland Sommarskog on 6 Jun 2010 17:06
neilr (neilryder(a)yahoo.com) 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, esquel(a)sommarskog.se

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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: event id 4879 MSDTC Client 2
Next: A way to set developer edition to behave like another edition