SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_get_object_permissions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_get_object_permissions]
GO


CREATE PROCEDURE sp_get_object_permissions
@user_name nvarchar(128) = null, /***User Name to check, if null then all users***/
@object_name nvarchar(128) = null /***Object Name to check, if null then all objects***/
AS

/****CREATED BY JASON BOHANON.  
FOR QUESTIONS AND COMMENTS PLEASE CONTACT ME AT bohanon@qwest.net. ****/

SELECT 
	sobj.name as 'Object Name', 
	CASE 	WHEN sobj.xtype = 'U'
			THEN 'USER TABLE'
		WHEN sobj.xtype = 'P'
			THEN 'STORED PROCEDURE'
		WHEN sobj.xtype = 'V'
			THEN 'VIEW'
		WHEN sobj.xtype = 'FN'
			THEN 'SCALAR FUNCTION'
		WHEN sobj.xtype = 'TF'
			THEN 'TABLE FUNCTION'
		WHEN sobj.xtype = 'S'
			THEN 'SYSTEM TABLE'
		ELSE 'UNKNOWN'
		END as 'Object Type',
	susr.name as 'User',
	CASE WHEN spro.action = 193
			THEN 'SELECT'
		     WHEN spro.action = 195
			THEN 'INSERT'
	             WHEN spro.action = 196
			THEN 'DELETE'
		     WHEN spro.action = 197
			THEN 'UPDATE'
		     WHEN spro.action = 224
			THEN 'EXECUTE'
		     WHEN spro.action = 26
			THEN 'REFERENCES'
		ELSE 'UNKNOWN TYPE: ' + CAST(spro.action as nvarchar(128))
		END as 'Permission Type'
FROM 
 	sysprotects spro
		JOIN 
	sysusers susr
			on spro.uid = susr.uid
		JOIN 
     	sysobjects sobj
			on sobj.id = spro.id
WHERE 
	susr.name like
	CASE 
		WHEN @user_name IS NOT NULL
			THEN @user_name
		ELSE
			'%'
	END
		AND
	sobj.name like 
	CASE
		WHEN @object_name IS NOT NULL
			THEN @object_name
		ELSE
		     	'%'
	END		
ORDER BY 
	sobj.xtype,
	susr.name, 
	sobj.name,
	spro.action

/***IF SUPPLIED @user_name IS A ROLE, OR A USER THAT IS PART OF A ROLE***/
IF @user_name IS NOT NULL and @user_name <> 'public'
 BEGIN---CHECK TO SEE IF @user_name IS A ROLE
	IF (SELECT 
		issqlrole 
	    FROM 
		sysusers
	    WHERE 
		name = @user_name) > 0
		
          	SELECT 
			susr2.name as 'Role Users'
		FROM 
			sysusers susr1 
				JOIN 
			sysmembers smbr
					on smbr.groupuid = susr1.uid
				JOIN 
			sysusers susr2 
					on smbr.memberuid = susr2.uid
		WHERE
			susr1.name = @user_name
	 ELSE
	     SELECT 
			susr1.name as 'User a Member In Role'
		FROM 
			sysusers susr1 
				JOIN 
			sysmembers smbr
					on smbr.groupuid = susr1.uid
				JOIN 
			sysusers susr2 
					on smbr.memberuid = susr2.uid
		WHERE
			susr2.name = @user_name
 END ---END ROLE CHECK


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO