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