April 27, 2006 at 10:34 pm
I run SQLserver 2000 and would like to copy the permissions of a DB user to a DB role if possible.
Can I generate scripts to do this or is there an easier way?
Bruce
April 28, 2006 at 7:46 am
The fatest way I can think of is open EM, right click th DB and choose "Generate SQL Script...", next press "Show All" button then select "Script All" checkbox. Now change to the "Formatting" tab and uncheck all items. Th proceed to the "Option Tab" and check (this being the only check) "Script object-level permissions". Finally select your output format (I would choose "Windows text (ANSI)" and "Create one file") and press ok and select the location and filename to save the file to.
Now open the file and do a search/replace for the current user accoun in question and set the value to the new role name. Thn save and execute this against the server. It won't remove the permissions from anything and permissions that already exist will not b affected by the fact you are running it again.
Finally, open you user and your role to verify the permissions look right an the fastest way to remove the users permissions is to reove them from the database (not the server) and then add them back and then to the role. Of course you can script a revoke of their permissions instead if you prefer.
And as always for safety sake make a backup before this operation to be sure you ca back out of the change.
April 30, 2006 at 6:05 pm
It seems the user is the owner of the DB objects, if logged in as that user I have owner permissions. i.e. the script does not grant permissions to the user in question.
thanks
Bruce
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply