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@UserIDINT,
@ObjectNameVARCHAR(50),
@ObjectTypeVARCHAR(2),
@SqlVARCHAR(4000),
@DBNameVARCHAR(50)

CREATE TABLE #tmpPermissions
       (ObjectIDINT,
ObjectNameVARCHAR(50),
ObjectTypeVARCHAR(2),
HasSelectTINYINT DEFAULT 0,
HasUpdateTINYINT DEFAULT 0,
HasReferenceTINYINT DEFAULT 0,
HasInsertTINYINTDEFAULT 0,
HasDeleteTINYINT DEFAULT 0,
HasExecuteTINYINT DEFAULT 0,
HasAllTINYINT DEFAULT 0,
UserIDINT)

SET @DBName = db_name()

SET @Sql = 
'SELECTO.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) +
'WHEREO.id = P.id' + CHAR(13) +
'ANDP.grantee = U.uid' + CHAR(13) +
'ANDU.name = ''' + @UserName + '''' + CHAR(13) 


INSERTINTO #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
SETHasSelect = 1
FROM#tmpPermissions AS T,' + CHAR(13) +
+ @DBName + '.dbo.syspermissions AS P' + CHAR(13) +
'WHERET.ObjectID = P.id
ANDT.ObjectType IN (''U'', ''V'', ''IF'', ''TF'')' + CHAR(13) +
'ANDP.actadd & 0x1 = 1' + CHAR(13) +
'ANDP.grantee = ' + CAST(@UserID AS VARCHAR)

EXEC (@Sql)

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

EXEC (@Sql)

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

EXEC (@Sql)

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

EXEC (@Sql)

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

EXEC (@Sql)

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

EXEC (@Sql)

/*Check all permission*/SET @Sql ='UPDATE#tmpPermissions
SETHasALL = 1
FROM#tmpPermissions AS T,' + CHAR(13) +
+ @DBName + '.dbo.syspermissions AS P' + CHAR(13) +
'WHERET.ObjectID = P.id' + CHAR(13) + 
'ANDT.ObjectType IN (''U'', ''V'', ''IF'', ''TF'')' + CHAR(13) +
'ANDP.actadd & 0x1F = 31'+ CHAR(13) +
'ANDP.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 (SELECT1 
FROM #tmpPermissions 
WHERE ObjectName = @ObjectName
ANDHasAll = 1)
BEGIN
SET @Sql = 'GRANT ' + 'ALL ON [' + @ObjectName + '] TO ' + @UserName
GOTO PrintStat
END

IF EXISTS (SELECT1 
FROM #tmpPermissions 
WHERE ObjectName = @ObjectName
ANDHasSelect = 1)
SET @Sql = 'GRANT ' + 'SELECT ,'

IF EXISTS (SELECT1 
FROM #tmpPermissions 
WHERE ObjectName = @ObjectName
ANDHasUpdate = 1)
SET @Sql = @Sql + 'UPDATE ,'

IF EXISTS (SELECT1 
FROM #tmpPermissions 
WHERE ObjectName = @ObjectName
ANDHasReference = 1)
SET @Sql = @Sql + 'REFERENCE ,'

IF EXISTS (SELECT1 
FROM #tmpPermissions 
WHERE ObjectName = @ObjectName
ANDHasInsert = 1)
SET @Sql = @Sql + 'INSERT ,'

IF EXISTS (SELECT1 
FROM #tmpPermissions 
WHERE ObjectName = @ObjectName
ANDHasDelete = 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