Scripting question
-
Currently running SQL Server 7 and NT 4, using NT authentification.
In the process of migrating to Server 2003. Current NT Domain is AcmeCorp. The database users look like..AcmeCorp/SalesUsers. Average of 150 stored procedures per database (10 databases). AcmeCorp/SalesUsers is granted execute in all of these stored procedures.
Once on Server 2003, management wants to create new domains. How can I script existing roles and permissions, then modify to accomodate the new domain name so as to avoid alot of clicking.
Existing Domain/User
AcmeCorp/SalesAdmin
New Domain/User
AC03/SalesAdmin_03
Hopefully this makes sense. Thanks for taking the time.
-
I might have missed something, but can't you just select all the stored procedures (CTRL-A) from within EM for a database, right click, Generate SQL Script, uncheck both the CREATE and DROP options from the Fromating tab, check Script object-level permissions from the options tab, generate it to a file, and open the file within any text editor or Query Analyzer and find/replace AcmeCorp with AC03? Yes I said "just".
You would need to do that once for each database. As for roles, I've never scripted them and don't know how to, but I found the following searching this site:http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=22&messageid=9772
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=6&messageid=3207
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=144626
Hope that helps.
-
For the database, create a user-defined database role. Assign it the permissions currently granted to AcmeCorp\SalesUsers. Then make the AcmeCorp\SalesUsers user a member of that role. You can use sp_helprotect to list out all the permissions AcmeCorp\SalesUsers currently has and with some text manipulation you should be able to create the GRANT EXECUTE statements needed for your database role. Repeat for the different permissions levels, etc. You get the idea. If you've already got database roles, sp_helprotect can be used to detail what permissions a particular role has and you could easily generate the proper permissions scripts from its output.
As far as the users within the database, you can use sp_helpuser to list all the users. You can use sp_helprolemember to list who are members of a given database role. You can build the scripts appropriately that recreate the membership of each database role. This may be affected by the logins if they don't match up. So there may have to be some manual work on your part.
Logins are a little more diffcult given your example. Your going to have to figure out how the logins are going to map from one domain to another. If the group names remain the same then you could use sp_helplogins to create your sp_grantlogin scripts.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply