List User 's Permissions in Database

USE master
GO
CREATE PROC sp_GetUserPermissions
	@UserName VARCHAR(50)
AS

/*
Author	: Minesh Shah (shah_mm@hotmail.com)
Purpose	: Prints Permissions of User in the database who has no dbo rights,
	  and print only permissions for user defined stored procedures, 
	  functions, tables and views belongs to dbo owner. 
	  Do not print column level permissions.
	  
	  You can use the print statements in new environment to grant permissions
	  or use to see what permission a user has in current environment.

Created	: 01/16/2004.

Usage	: USE "DatabaseName"
	  Exec sp_GetUserPermissions "UserName"


*/

BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE	@UserID		INT,
	@ObjectName	VARCHAR(50),
	@ObjectType	VARCHAR(2),
	@Sql		VARCHAR(4000),
	@DBName		VARCHAR(50)

CREATE TABLE #tmpPermissions
       (ObjectID	INT,
	ObjectName	VARCHAR(50),
	ObjectType	VARCHAR(2),
	HasSelect	TINYINT DEFAULT 0,
	HasUpdate	TINYINT DEFAULT 0,
	HasReference	TINYINT DEFAULT 0,
	HasInsert	TINYINT	DEFAULT 0,
	HasDelete	TINYINT DEFAULT 0,
	HasExecute	TINYINT DEFAULT 0,
	HasAll		TINYINT DEFAULT 0,
	UserID		INT)

SET @DBName = db_name()

SET @Sql = 
	'SELECT	O.ID, O.name, O.xtype, U.uid
	FROM	' + @DBName + '.dbo.sysobjects AS O,' + CHAR(13) +
		  + @DBName + '.dbo.syspermissions AS P,' + CHAR(13) +
		  + @DBName + '.dbo.sysusers AS U' + CHAR(13) +
	'WHERE	O.id = P.id' + CHAR(13) +
	'AND	P.grantee = U.uid' + CHAR(13) +
	'AND	U.name = ''' + @UserName + '''' + CHAR(13) 


INSERT	INTO #tmpPermissions (ObjectID, ObjectName, ObjectType, UserID)
EXEC (@Sql)

SET @UserID = (SELECT DISTINCT UserID FROM #tmpPermissions)

IF @UserID IS NULL
BEGIN
	RAISERROR('Either the given user does not exists or does not have any permissions in this database', 11, 127)
	RETURN
END

/*Check Select Permission*/
SET @Sql ='UPDATE	#tmpPermissions
	SET	HasSelect = 1
	FROM	#tmpPermissions AS T,' + CHAR(13) +
		+ @DBName + '.dbo.syspermissions AS P' + CHAR(13) +
	'WHERE	T.ObjectID = P.id
	AND	T.ObjectType IN (''U'', ''V'', ''IF'', ''TF'')' + CHAR(13) +
	'AND	P.actadd & 0x1 = 1' + CHAR(13) +
	'AND	P.grantee = ' + CAST(@UserID AS VARCHAR)

EXEC (@Sql)

/*Check Update permission*/
SET @Sql ='UPDATE	#tmpPermissions
	SET	HasUpdate = 1
	FROM	#tmpPermissions AS T,' + CHAR(13) +
		+ @DBName + '.dbo.syspermissions AS P' + CHAR(13) +
	'WHERE	T.ObjectID = P.id
	AND	T.ObjectType IN (''U'', ''V'', ''IF'', ''TF'')' + CHAR(13) +
	'AND	P.actadd & 0x2 = 2' + CHAR(13) +
	'AND	P.grantee = ' + CAST(@UserID AS VARCHAR)

EXEC (@Sql)

/*Check Reference permission*/
SET @Sql ='UPDATE	#tmpPermissions
	SET	HasReference = 1
	FROM	#tmpPermissions AS T,' + CHAR(13) +
		+ @DBName + '.dbo.syspermissions AS P' + CHAR(13) +
	'WHERE	T.ObjectID = P.id
	AND	T.ObjectType IN (''U'', ''V'', ''IF'', ''TF'')' + CHAR(13) +
	'AND	P.actadd & 0x4 = 4'+ CHAR(13) +
	'AND	P.grantee = ' + CAST(@UserID AS VARCHAR)

EXEC (@Sql)

/*Check Insert permission*/
SET @Sql ='UPDATE	#tmpPermissions
	SET	HasInsert = 1
	FROM	#tmpPermissions AS T,' + CHAR(13) +
		+ @DBName + '.dbo.syspermissions AS P' + CHAR(13) +
	'WHERE	T.ObjectID = P.id
	AND	T.ObjectType IN (''U'', ''V'', ''IF'', ''TF'')' + CHAR(13) +
	'AND	P.actadd & 0x8 = 8'+ CHAR(13) +
	'AND	P.grantee = ' + CAST(@UserID AS VARCHAR)

EXEC (@Sql)

/*Check Delete permission*/
SET @Sql ='UPDATE	#tmpPermissions
	SET	HasDelete = 1
	FROM	#tmpPermissions AS T,' + CHAR(13) +
		+ @DBName + '.dbo.syspermissions AS P' + CHAR(13) +
	'WHERE	T.ObjectID = P.id
	AND	T.ObjectType IN (''U'', ''V'', ''IF'', ''TF'')' + CHAR(13) +
	'AND	P.actadd & 0x10 = 16'+ CHAR(13) +
	'AND	P.grantee = ' + CAST(@UserID AS VARCHAR)

EXEC (@Sql)

/*Check Exec permission*/
SET @Sql ='UPDATE	#tmpPermissions
	SET	HasExecute = 1
	FROM	#tmpPermissions AS T,' + CHAR(13) +
		+ @DBName + '.dbo.syspermissions AS P' + CHAR(13) +
	'WHERE	T.ObjectID = P.id
	AND	T.ObjectType IN (''P'', ''FN'')' + CHAR(13) +
	'AND	P.actadd & 0x10 = 16'+ CHAR(13) +
	'AND	P.grantee = ' + CAST(@UserID AS VARCHAR)

EXEC (@Sql)

/*Check all permission*/
SET @Sql ='UPDATE	#tmpPermissions
	SET	HasALL = 1
	FROM	#tmpPermissions AS T,' + CHAR(13) +
		+ @DBName + '.dbo.syspermissions AS P' + CHAR(13) +
	'WHERE	T.ObjectID = P.id' + CHAR(13) + 
	'AND	T.ObjectType IN (''U'', ''V'', ''IF'', ''TF'')' + CHAR(13) +
	'AND	P.actadd & 0x1F = 31'+ CHAR(13) +
	'AND	P.grantee = ' + CAST(@UserID AS VARCHAR)

EXEC (@Sql)

DECLARE Permission_Cur 	CURSOR FOR
	SELECT 	ObjectName, ObjectType
	FROM 	#tmpPermissions
	ORDER BY ObjectType

OPEN Permission_Cur

FETCH NEXT FROM Permission_Cur INTO @ObjectName, @ObjectType

WHILE @@fetch_status = 0
BEGIN
	IF @ObjectType IN ('P', 'FN')
		SET @Sql = 'GRANT EXEC ON [' + @ObjectName + '] TO ' + @UserName
	
	IF @ObjectType IN ('U', 'V', 'IF', 'TF')
	BEGIN
		SET @Sql = 'GRANT '

		IF EXISTS (	SELECT	1 
				FROM 	#tmpPermissions 
				WHERE 	ObjectName = @ObjectName
				AND	HasAll = 1)
		BEGIN
			SET @Sql = 'GRANT ' + 'ALL ON [' + @ObjectName + '] TO ' + @UserName
			GOTO PrintStat
		END
		
		IF EXISTS (	SELECT	1 
				FROM 	#tmpPermissions 
				WHERE 	ObjectName = @ObjectName
				AND	HasSelect = 1)
			SET @Sql = 'GRANT ' + 'SELECT ,'

		IF EXISTS (	SELECT	1 
				FROM 	#tmpPermissions 
				WHERE 	ObjectName = @ObjectName
				AND	HasUpdate = 1)
			SET @Sql = @Sql + 'UPDATE ,'

		IF EXISTS (	SELECT	1 
				FROM 	#tmpPermissions 
				WHERE 	ObjectName = @ObjectName
				AND	HasReference = 1)
			SET @Sql = @Sql + 'REFERENCE ,'

		IF EXISTS (	SELECT	1 
				FROM 	#tmpPermissions 
				WHERE 	ObjectName = @ObjectName
				AND	HasInsert = 1)
			SET @Sql = @Sql + 'INSERT ,'

		IF EXISTS (	SELECT	1 
				FROM 	#tmpPermissions 
				WHERE 	ObjectName = @ObjectName
				AND	HasDelete = 1)
			SET @Sql = @Sql + 'DELETE '

		SET @Sql = LEFT(@Sql, LEN(@Sql)-1) + ' ON [' + @ObjectName + '] TO ' + @UserName
		
	END

	PrintStat:
	PRINT @Sql

	FETCH NEXT FROM Permission_Cur INTO @ObjectName, @ObjectType
END

CLOSE Permission_Cur
DEALLOCATE Permission_Cur

DROP TABLE #tmpPermissions

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO