List User 's Permissions in Database
Do you ever need to look for a given user's permissions for security reasons or move the permissions for that user from test environment to production? This is a script, which will generate print statements, which can be use to view/grant, the permissions of all dbo objects, which the given user has, which is not in the dbo role. Create this script in the master database so you can run in any database. Tested in sql2K. Please send your commnets/suggestions at shah_mm@hotmail.com
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