I am involved in a SharePoint 2010 SQL Server migration, and as you may know, you have follow this article:
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!