August 4, 2011 at 10:58 pm
Does anyone have script to transfer the users permission from one SQL Server to another SQL Server where the database matches on both server.
I already moved the users and logins but now looking for some script which can generate the permission of logins across all the databases.
For example:- login "a" have the permission on 5 Database in Server1. Server 2 also have the same 5 database. Now the script required to generate the permission on all 5 database which I can execute on Server 2.
Also script should not specific to one user but for all user in current database and permission across the current SQL server(server 1)
----------
Ashish
August 5, 2011 at 12:06 am
Back in 2009 I created a set of scripts to generate T-SQL statements for all server and database principals and their rights from an existing server/database. The generated statements can later be run to re-create the exact same set of principals and rights as they were at the time the scripts were created. Since the generated scripts are plain T-SQL statements, you may decide to run them against another server/database. In effect that transfers the 1st server/databases principals and their rights onto that server/database.
One of these scripts you can see here: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/62003432-efaf-4915-bd75-fff804574276.
If this is the sort of thing you're looking for I can go and dig up the entire set for you. I haven't looked at them since I created them, but they will still work for pretty most everything.
August 5, 2011 at 12:44 am
Thanks for the msdn link. It created the user,roles and login. I am looking for some script which can generate all permission for all users in a perticular database.
I have this script
SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + ''''
from sys.database_principals users
inner join sys.database_role_members link
on link.member_principal_id = users.principal_id
inner join sys.database_principals roles
on roles.principal_id = link.role_principal_id
but this script generates the permission for a specific database only.
I want the same script for across all the database and where I should have the option to pass the login name if I want, else it should generate for all.
----------
Ashish
August 5, 2011 at 6:31 am
I know, this was the only one I knew for sure where it was online. 🙂
In the attachment is the complete set of scripts.
- logins,
- server permissions
- server role members
- database principals,
- database permissions
- database role members
Plus some other things (I'm not sure any more what these were for, it's over 2 years ago)
Oh and don't forget you need to be logged on as sysadmin or such or you won't be shown all information. And anything you can't see, won't be scripted...
August 5, 2011 at 7:07 am
Thanks for sharing. I will have a look.
----------
Ashish
August 8, 2011 at 8:38 am
I know, since they were created over 2 years ago, that I would probably implement parts of the scripts differently now (f.e. converting the password binaries into hex can probably be done more efficient). If you've got comments or suggestions for improvement, please let me know, or better put them in this thread so that others can take profit from them too.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply