How to check all users permissions in SQL Server

Hi everyone!

I am involved in a SharePoint 2010 SQL Server migration, and as you may know, you have follow this article:

https://technet.microsoft.com/en-us/library/cc512723(v=office.14).aspx

Truth is, that you need to update as well the users permissions in the destination instance. So the immediate question would be: How should I get all the permissions in my instance?

For that I found some blogs and articles showing how to check permissions in one single database, but I needed to check the whole instance, that would mean create a cursor, browse along the databases and finally execute the script that checks. so I created this script for that:

 if object_id('tempdb..#mitabla') is null
begin
 select 
 CONVERT(char(255), '') as databasename,
 convert(char(45),'') as srvLogin, 
  convert(char(45),'') as srvRole,
    convert(char(25),'') as dbUser,
 convert(char(25),'') as dbRole
  into #mitabla
end
else
begin
   delete from #mitabla
end

declare @mydatabase nvarchar(60)

declare bases_de_datos cursor
for
select
   name
from
    sys.databases


   open bases_de_datos
 fetch bases_de_datos into @mydatabase
   while @@FETCH_STATUS = 0
    begin

   declare @comando nvarchar(1024)
 print @comando
  set @comando = ' 
   use ' + @mydatabase + ' 
    insert into
     #mitabla
    (
       databasename,
       srvLogin, 
      srvRole,
        dbUser,
     dbRole
  )
   select 
 ''' + @mydatabase + ''' as databasename,
    convert(char(45),sp.name) as srvLogin, 
 convert(char(45),sp2.name) as srvRole,
  convert(char(25),dbp.name) as dbUser,
   convert(char(25),dbp2.name) as dbRole
   from 
   sys.server_principals as sp join
    sys.database_principals as dbp on sp.sid=dbp.sid join
   sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
 sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join 
  sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
 sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id  '
   
    fetch bases_de_datos into @mydatabase
   
    execute (@comando)
end
close bases_de_datos
deallocate bases_de_datos

select *  from #mitabla

 Hope this will help you!