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:

http://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!

 


Comments (0)

Skip to main content