SP gets user rights and prints out batch-weighted statements

ALTER PROCEDURE [dbo]. [Usp_showuserpermission]
As
BEGIN
DECLARE @px INT = 0
DECLARE @loginuser VARCHAR (max);
DECLARE @dbname VARCHAR (MAX)
DECLARE @sql VARCHAR (MAX)
SELECT @loginuser = System_user;
IF object_id (' tempdb.. #dbs ', ' U ') is not NULL
DROP TABLE #dbs
IF object_id (' tempdb.. #permiss ', ' U ') is not NULL
DROP TABLE #permiss
IF object_id (' tempdb.. #execsql ', ' U ') is not NULL
DROP TABLE #execsql
CREATE TABLE #execsql
(
ID INT IDENTITY (1, 1),
ESQL VARCHAR (max)
)
CREATE TABLE #permiss
(
[dbname] [varchar] () not NULL,
[User] [sysname] Not NULL,
[Usertype] [nvarchar] () NULL,
[Permission] [nvarchar] (+) NULL,
[PermissionState] [nvarchar] () NULL,
[ObjectName] [nvarchar] (+) NULL,
[Schema] [nvarchar] (+) NULL,
[ObjectType] [varchar] () NULL,
[Permissionclass] [nvarchar] (%) NULL
)
SELECT id = IDENTITY (INT, +),
Name
Into #dbs
From Master.sys.databases DB
WHERE db.database_id > 4
and Db.name not in (' Dbcenter ', ' reportserver ', ' reportservertempdb ', ' targetdb ', ' Initiatordb ')
while (@px < (SELECT MAX (ID)
From #dbs
) )
BEGIN
SELECT TOP 1
@dbname = #dbs. Name
From #dbs
SET @sql = ' use ' + @dbname + ' SELECT ' + ' + @dbname
+ ' + ' as ' dbname ', c.name as ' user ',
C.type_desc as ' usertype ',
A.permission_name as ' permission ',
A.state_desc as ' PermissionState ',
B.name ' ObjectName ',
Object_schema_name (a.major_id) as ' SCHEMA ',
Case B.type
When ' AF ' and then ' Aggregate function (CLR) '
When "C" then "CHECK constraint"
When "D" and then "DEFAULT (constraint or stand-alone)"
When "F" then "FOREIGN KEY constraint"
When "PK" then "PRIMARY KEY constraint"
When "P" then "SQL stored Procedure"
When "PC" then "Assembly (CLR) stored procedure"
When ' FN ' and ' then ' SQL scalar function '
When the ' FS ' Then ' Assembly (CLR) scalar function '
When "FT" then "Assembly (CLR) table-valued function"
When "R" then "Rule (Old-style, stand-alone)"
When ' RF ' and ' Then ' replication-filter-procedure '
When "S" then "System base table"
When "SN" then "synonym"
When the ' SQ ' then ' Service queue '
When "TA" then "Assembly (CLR) DML trigger"
When ' TR ' and ' then ' SQL DML Trigger '
When "IF" then "SQL inline table-valued function"
When "TF" then "SQL table-valued-function"
When the ' U ' Then ' Table ([user-defined]) '
When ' UQ ' and ' Then ' UNIQUE constraint '
When "V" then "View"
When "X" then "Extended stored Procedure"
When ' IT ' and ' Then ' Internal table '
END ' ObjectType ',
A.class_desc ' Permissionclass ' from ' + @dbname
+ '. Sys.database_principals C
Left JOIN ' + @dbname
+ '. Sys.database_permissions A on c.principal_id = a.grantee_principal_id
Left JOIN ' + @dbname + '. Sys.sysobjects b on b.id = a.major_id where C.type = "S" and C.name Not in (' dbo ', ' sys ', ' Sa ', ' guest ', ' information_schema ', ' '
+ @loginuser + ') '

--database_principals type=s sqluser,type=r database role
INSERT into #permiss
EXEC (@sql
)
SET @px = @px + 1
DELETE #dbs
WHERE name = @dbname
END
SELECT * from #permiss
Select ' Use ' + dbname + ' IF EXISTS (select NAME from sys.sysusers WHERE name= ' ' +[user]+ ')
BEGIN DROP USER ' +[user]+ ' END
CREATE USER ' +[user]+ ' as CreateUser from #permiss GROUP by Dbname,[user]
INSERT #execsql
SELECT ' use ' + dbname
+case permissionclass when ' object_or_column ' and ' Grant ' +permission+ ' on ' +objectname+ ' to ' +[user] when ' DATABASE '
Then ' Grant ' +permission+ ' to ' +[user] END
From #permiss WHERE permission <> ' CONNECT '
SELECT * from #execsql
END

SP gets user rights and prints out batch-weighted statements