Tuesday, July 17, 2007

Scripting SQL Server Roles

For our prime contract, we have multiple applications all using the same database. We choose this schema because there is some information that is shared among all these applications, and rather than having it duplicated, we decided to just shove it all into the same place. Additionally, we have users tacked onto the databases, for each application which owns the objects of that application.

This allows us to have two tables named the same, but schema'd out to the application. For example, the EPB.Documents table and the ELETA.Documents table. While they are named the same, they serve distinctly different purposes. The EPB.Documents table is the meta data for the documents which EPB generates. The ELETA.Documents table is attribute data for documents displayed by ELETA.

Little things like that.

Anyways, in addition to all of that, we use Roles to control access to the application. For example there is an EPBUser role and a ELETAUser role. This means that we can quickly assign access to the db user to those elements which each application needs to function.

The problem comes in with generating SQL script files for backup/configuration/deployment purposes. 2000's Enterprise Manager has no support for scripting the role to a file or a query window. 2005's SQL Server Studio does, but it only scripts the creation of that role, not all the underlying permissions.

So, I hunted the internet and found this. What this does is displays just what attributes a user or a role has (there are 2 scripts on that link, the top one is for users the bottom for roles).

I grabbed it, plugged the name of my role in, and within seconds I had a nice batch of GRANT statements which the role needs to run the application.


Anonymous said...

Silly guy, I've told you it'll script roles if you choose a table first (2000).

Plus, there are a whole slew of Master DB table and views that probably have all the information you want (see Information_Schema in Master DB for 2000 and 2005).

It's good you found a tool, but you could've built a simple query yourself.


Stephen Wrighton said...

Yeah, but I don't ever remember any of the stuff you tell me. And Master DB tables are arcane.

If I select the role, and tell it to script the role, it should automatically script the permissions with the role.

Blog Widget by LinkWithin