November 19, 2001 at 7:35 am
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
November 19, 2001 at 9:54 am
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
November 19, 2001 at 9:57 am
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
November 19, 2001 at 10:00 am
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
November 19, 2001 at 10:03 am
Thanx, I will have a look and see if I can use it.
November 19, 2001 at 10:04 am
November 19, 2001 at 10:33 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply