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](60) NOT NULL ,

[user] [sysname] NOT NULL ,

[userType] [nvarchar](60) NULL ,

[permission] [nvarchar](128) NULL ,

[PermissionState] [nvarchar](60) NULL ,

[ObjectName] [nvarchar](128) NULL ,

[Schema] [nvarchar](128) NULL ,

[ObjectType] [varchar](60) NULL ,

[PermissionClass] [nvarchar](60) NULL

)

SELECT id = IDENTITY( INT ,1,1 ),

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'' THEN ''Aggregate function (CLR)''

WHEN ''C'' THEN ''CHECK constraint''

WHEN ''D'' 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'' THEN ''SQL scalar function''

WHEN ''FS'' THEN ''Assembly (CLR) scalar function''

WHEN ''FT'' THEN ''Assembly (CLR) table-valued function''

WHEN ''R'' THEN ''Rule (old-style, stand-alone)''

WHEN ''RF'' THEN ''Replication-filter-procedure''

WHEN ''S'' THEN ''System base table''

WHEN ''SN'' THEN ''Synonym''

WHEN ''SQ'' THEN ''Service queue''

WHEN ''TA'' THEN ''Assembly (CLR) DML trigger''

WHEN ''TR'' THEN ''SQL DML trigger''

WHEN ''IF'' THEN ''SQL inline table-valued function''

WHEN ''TF'' THEN ''SQL table-valued-function''

WHEN ''U'' THEN ''Table ([user-defined])''

WHEN ''UQ'' THEN ''UNIQUE constraint''

WHEN ''V'' THEN ''View''

WHEN ''X'' THEN ''Extended stored procedure''

WHEN ''IT'' 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' THEN ' grant '+permission+' on '+ObjectName+' to '+[user] WHEN 'DATABASE'

THEN ' grant '+permission+' to '+[user] END

FROM #permiss WHERE permission <>'CONNECT'

SELECT * FROM #execsql

END