RE: Scripting SQL server security

Hi Chris,

 

This seems such a useful idea.  If you really want to make it easy for
posterity to find, why not answer your own question on StackOverflow?

 

Happy New Year!

 

Cheers,

 

Richard

 

 <mailto:richard-yP/[email protected]> richard-yP/[email protected]

www.dynamisys.co.uk

(L) 01793 731225

(M) 07769 266522

Dynamisys is registered in the UK - number 4152561

 

From: [email protected]
[mailto:[email protected]] On Behalf Of Chris Wuestefeld
Sent: 31 December 2009 20:18
To: [email protected]
Subject: [OT] Scripting SQL server security

 

  

This morning I had a minor disaster on my development DB, nuking all of the
permissions for access to my stored procedures. I wanted to recreate them
from our production server, but had a heck of a time figuring out how to do
this. I couldn't find anything I could directly plagiarize from Google, so I
had to invent it myself. It turns out that if you understand the various
catalog views, it's straightforward, but it took a bit of research to get
there. 

I thought I'd post my result, in case anyone else finds it useful. This
query dumps out a bunch of GRANT commands, so that you can copy them back
into another query window and execute them on the server that's to receive
the permissions.

SELECT 'GRANT ' + dp.permission_name collate latin1_general_cs_as

      + ' ON ' + s.name + '.' + o.name + ' TO ' + dpr.name 

    FROM sys.database_permissions AS dp

    INNER JOIN sys.objects AS o ON dp.major_id=o.object_id

    INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id

    INNER JOIN sys.database_principals AS dpr ON
dp.grantee_principal_id=dpr.principal_id

    WHERE dpr.name NOT IN ('public','guest')

    AND permission_name='EXECUTE'

Of course, you might want to comment out the last line if you need to dump
permissions for objects other than stored procedures.