We are currently running sql 2000 and are moving our database onto sql 2005 running on a different box.
We have managed to move the entire database, with users however the users permissions on specific tables/views/stored procedures have not been transferred, does anyone know a way of transferring user permissions rather then doing them all by hand?
The system is a large (over 500 table/views/stored procedures) and a very active one and therefore downtime is not optional.
any suggestions would be appreciated
Regards
Chris V
I think you can script those certain objects and execute the script in sql 2005 to create the same as in sql 2000
|||The users permissions should have been taken across when you moved the databases (did you user a backup/restore method?). However, unless you also moved the master database then these users may well have lost their associated logins. You should be able to fix this by using sp_change_users_login.
However, if the permissions have gone AWOL, i think you will have to just script out the permissions from the old server and apply this script to the new server. I've seen a neat script somewhere using SQLDMO which will do all the permissions in one but alas, i cannot find it right now though i did see this SQL script which should do a similar job albeit on a user by user/role by role basis:-
http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx
A final note, this sort of thing can be avoided by adding all your object level permissions to Roles which are transferred with the database. Users can then just be added to the role again and the problem should be solved.
Anyway, good luck.
HTH!
