Creating script to grant permissions

  • Hi

    I would like to know if there is a way to create a script of all the current permissions for the groups on a database.

    What I need it for is that all the groups are dropped on a certain database and the all the groups are recreated and there users added. I need a script to run all the previous permissions for that database.

    I know you can do it with Enterprise Manager and the Generate Script button. But I do not want to use this option. I would just like to run a script that builds all the relevant permission statements.

    Regards

  • I ran a quick trace on sql 2000 and didn't see what EM is running? Why not use EM?

    I am sure you can script something using the system tables, but I wouldn't recommend it. I'd say use DMO first.

    Steve Jones

    steve@dkranch.net

  • Hi Steve,

    The reason for not using EM is time. It takes some time to go through all the databases using EM. If there is a script option, I could use it to cycle through each database and get the permissions.

    As for DMO, I have never used it. I have no idea where to start with that.

    Regards

    Gert

  • Andy has an introductory article for this.

    http://www.sqlservercentral.com/columnists/awarren/dmointro.asp

    There are third party products that can do this as well.

    Steve Jones

    steve@dkranch.net

  • Thanx, I will have a look and see if I can use it.

  • Good luck. If I find another way, I will post it.

    Steve Jones

    steve@dkranch.net

  • Checked through BOL. Only shows EM and DMO as auto solutions.

    For DMO: Here is the section from BOL.

    Script Method

    The Script method generates a Transact-SQL command batch that can be used to re-create the Microsoft® SQL Server™ 2000 component referenced by the SQL-DMO object.

    Syntax

    object.Script( [ ScriptType ] [, ScriptFilePath ] [, Script2Type ] ) as String

    Steve Jones

    steve@dkranch.net

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply