Use XML to define SQL Server security?
-
We have a number of dev and test SQL Servers (2005) and I would like a more structured and manageable way to setup access/security on these test/dev servers. Currently, it's a time-consuming process because I generally have to manually setup whatever Logins/Security the developers want in test/dev on an ad-hoc basis. Sometimes it's a combination of copying Logins from production and creating test specific logins/security. When the test db's get refreshed/restored, I often end up having to start again - although I try to get the developers to script out the test environment specific security stuff. I was wondering if I could write/buy a utility, like an ASP.NET app that helped developers to input the required Logins, access and so on into a form and have the app save this info as an XML file. The XML file could then in turn be used as in input into another ASP.NET app using SMO to apply the security model defined in the XML file. It would also be necessary to be able to point the app at an existing server, production for example, and generate a template - ie. populate the XML file with that server's Logins, db access, schema info (db owner), db roles, role membership and permissions. I can code this up if necessary and SMO should gve me what I want. However, given that someone else might have already done this, maybe I could save myself a lot of time and effort. Thoughts or suggestions?