show all permissions for user defined role

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

show all permissions for user defined role

Author

Topic

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2001-10-11 : 21:31:14

I read a post a while ago:

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9581&FORUM_ID=6&CAT_ID=3&Topic_Title=User+Defined+Role&Forum_Title=Administration[/url]

I came up with a partial answer for it, then spent more (i.e. too much) time trying to finish it. So here it is. Right after I finished this, one of the devs I work with said oh yeah, sp_helprotect does most of that. C'est La Vie.

This script creates 2 functions: The first takes a hex value, converts it to a string, and then to 1s and 0s through a series of REPLACEs. The second function takes that string and builds a column list based on the bitmap that binary string represents. There are a number of ways to do this, but I found building and outputting a string was the most flexible, given various function limitations. The proc then calls the second function to read the bitmap of a column list in sysprotects so that you can see column level permissions for user defined roles. If you call the proc with no role name, it gives you summary permissions for all the user defined roles and public in the db you're in. If you call it with a role name, you get column level info. If you run the whole script, it will add a test role in pubs and then run the proc.

Please let me know if anyone has different/better ways to go about doing what I've done here -- particularly in reading the bitmap.

(Error handling is left to someone else. After spending so much time on this, I would nerver dream of passing it anything but pristine input).

And special thanks to stocksr who saved my life with this:

CHARINDEX(',' + CAST(someIntField AS varchar) + ',' , ',' + @passedList + ',') > 0)

----------------------------------------------------
USE MASTER
GO

IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'fn_HexToBinStr')
	DROP FUNCTION fn_HexToBinStr
GO

CREATE FUNCTION fn_HexToBinStr (@Hex VARBINARY(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @OutputString VARCHAR(8000)
  SELECT @OutputString = 
  REPLACE(
   REPLACE(
    REPLACE(
     REPLACE(
      REPLACE(
       REPLACE(
        REPLACE(
         REPLACE(
          REPLACE(
           REPLACE(
            REPLACE(
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  RIGHT(master.dbo.fn_varbintohexstr(@Hex),
                       (LEN(master.dbo.fn_varbintohexstr(@Hex))-2))
                        ,'0','0000')
                       ,'1','0001')
                      ,'2','0010')
                     ,'3','0011') 
                    ,'4','0100') 
                   ,'5','0101') 
                  ,'6','0110') 
                 ,'7','0111') 
                ,'8','1000') 
               ,'9','1001') 
              ,'A','1010') 
             ,'B','1011') 
            ,'C','1100') 
           ,'D','1101') 
          ,'E','1110') 
         ,'F','1111')
  RETURN(@OutputString)
END
GO
-----------------------------------------------------------------
USE master
GO

IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'fn_HexToColList')
	DROP FUNCTION fn_HexToColList
GO

CREATE FUNCTION fn_HexToColList (@Hex VARBINARY(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @BinStr VARCHAR(8000)
  DECLARE @BinStrLen INT
  DECLARE @StartCnt INT
  DECLARE @Position INT
  DECLARE @OutputString VARCHAR(8000)
  DECLARE @Tmp CHAR(1)
  
  SET @BinStr = master.dbo.fn_HexToBinStr(@Hex)
  SET @OutputString = ''
  SET @BinStrLen  = DATALENGTH(@BinStr)
  SET @StartCnt = @BinStrLen
  SET @Position = @BinStrLen
  
  WHILE @Position >= 1
    BEGIN
      SET @Tmp = SUBSTRING(@BinStr, @Position, 1)
      IF (@Tmp = 1)
          SET @OutputString = @OutputString + RTRIM(CAST((@StartCnt - 8) + (@StartCnt - @Position)AS CHAR)) + ','
          SET @Position = @Position - 1
          IF ((@Position % 8) = 0)
            SET @StartCnt = @StartCnt - 8 
     END
  RETURN (LEFT(@OutputString,(LEN(@OutputString)-1)))
END
GO
-----------------------------------------------------------
USE master
GO

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'sp_userdefinedrolepermission' 
	   AND 	  type = 'P')
    DROP PROCEDURE sp_userdefinedrolepermission
GO

CREATE PROC sp_userdefinedrolepermission
@role SYSNAME = NULL
AS
IF (@role IS NULL)
  BEGIN
    SELECT u.name AS [Role], o.name AS [Object], 
    [Permission] = CASE  p.action
      WHEN 26  THEN 'REFERENCES'
      WHEN 178 THEN 'CREATE FUNCTION'
      WHEN 193 THEN 'SELECT'
      WHEN 195 THEN 'INSERT'
      WHEN 196 THEN 'DELETE'
      WHEN 197 THEN 'UPDATE'
      WHEN 198 THEN 'CREATE TABLE'
      WHEN 203 THEN 'CREATE DATABASE'
      WHEN 207 THEN 'CREATE VIEW'
      WHEN 222 THEN 'CREATE PROCEDURE'
      WHEN 224 THEN 'EXECUTE'
      WHEN 228 THEN 'BACKUP DATABASE'
      WHEN 233 THEN 'CREATE DEFAULT'
      WHEN 235 THEN 'BACKUP LOG'
      WHEN 236 THEN 'CREATE RULE'
    END,
    [Grant Type] = CASE p.protecttype
      WHEN 204 THEN 'GRANT_W_GRANT'
      WHEN 205 THEN 'GRANT'
      WHEN 206 THEN 'REVOKE'
    END,
    u2.name AS [Grantor]
    
    FROM sysusers u 
    JOIN sysprotects p
    ON u.uid = p.uid
    JOIN sysusers u2
    ON u2.uid = p.grantor
    JOIN sysobjects o
    ON p.id = o.id
    WHERE (u.issqlrole = 1 or u.isapprole = 1)
    ORDER BY [Role],[Object],[Permission]
  END
ELSE
  BEGIN
    SELECT u.name AS [Role], o.name AS [Object], 
    [Permission] = CASE  p.action
      WHEN 26  THEN 'REFERENCES'
      WHEN 178 THEN 'CREATE FUNCTION'
      WHEN 193 THEN 'SELECT'
      WHEN 195 THEN 'INSERT'
      WHEN 196 THEN 'DELETE'
      WHEN 197 THEN 'UPDATE'
      WHEN 198 THEN 'CREATE TABLE'
      WHEN 203 THEN 'CREATE DATABASE'
      WHEN 207 THEN 'CREATE VIEW'
      WHEN 222 THEN 'CREATE PROCEDURE'
      WHEN 224 THEN 'EXECUTE'
      WHEN 228 THEN 'BACKUP DATABASE'
      WHEN 233 THEN 'CREATE DEFAULT'
      WHEN 235 THEN 'BACKUP LOG'
      WHEN 236 THEN 'CREATE RULE'
    END,
    [Grant Type] = CASE p.protecttype
      WHEN 204 THEN 'GRANT_W_GRANT'
      WHEN 205 THEN 'GRANT'
      WHEN 206 THEN 'REVOKE'
    END,
    u2.name AS [Grantor]
    
    FROM sysusers u 
    JOIN sysprotects p
    ON u.uid = p.uid
    JOIN sysusers u2
    ON u2.uid = p.grantor
    JOIN sysobjects o
    ON p.id = o.id
    WHERE u.name = @role
    ORDER BY [Role],[Object],[Permission]

    SELECT o.name AS [Table], c.name AS [Column], 
    [Permission] = CASE  p.action
      WHEN 26  THEN 'REFERENCES'
      WHEN 178 THEN 'CREATE FUNCTION'
      WHEN 193 THEN 'SELECT'
      WHEN 195 THEN 'INSERT'
      WHEN 196 THEN 'DELETE'
      WHEN 197 THEN 'UPDATE'
      WHEN 198 THEN 'CREATE TABLE'
      WHEN 203 THEN 'CREATE DATABASE'
      WHEN 207 THEN 'CREATE VIEW'
      WHEN 222 THEN 'CREATE PROCEDURE'
      WHEN 224 THEN 'EXECUTE'
      WHEN 228 THEN 'BACKUP DATABASE'
      WHEN 233 THEN 'CREATE DEFAULT'
      WHEN 235 THEN 'BACKUP LOG'
      WHEN 236 THEN 'CREATE RULE'
    END,
    [Grant Type] = CASE p.protecttype
      WHEN 204 THEN 'GRANT_W_GRANT'
      WHEN 205 THEN 'GRANT'
      WHEN 206 THEN 'REVOKE'
    END,
    --p.columns AS [Bitmap], - for debugging
    u2.name AS [Grantor] --,
    --c.colid - for debugging
    
    FROM sysusers u 
    JOIN sysprotects p
    ON u.uid = p.uid
    JOIN sysusers u2
    ON p.grantor = u2.uid
    JOIN sysobjects o
    ON p.id = o.id
    JOIN syscolumns c
    ON o.id = c.id
    
    WHERE 
    p.columns IS NOT NULL
    AND p.columns <> 0x01
    AND u.name = @role
    AND (CHARINDEX(',' + CAST(c.colid AS varchar) + ',' , ',' + master.dbo.fn_HexToColList(p.columns) + ',') > 0)
    ORDER BY [Table],[Column],[Permission]
  END
GO
-----------------------------------------------------------------------

USE pubs
GO
IF EXISTS(SELECT name 
          FROM sysusers 
          WHERE name = 'TestMBProc')
      EXEC sp_droprole 'TestMBProc'
GO
EXEC sp_addrole 'TestMBProc'
GO
GRANT UPDATE,SELECT ON authors(au_id,au_lname,au_fname) TO TestMBProc WITH GRANT OPTION
GO
GRANT INSERT,SELECT ON jobs TO TestMBProc
GO
GRANT EXECUTE ON byroyalty TO TestMBProc
GO
GRANT EXECUTE ON dt_addtosourcecontrol TO TestMBProc
GO
EXEC sp_userdefinedrolepermission 'TestMBProc'
GO
EXEC sp_userdefinedrolepermission
GO

/* --------------------  cleanup ------------------------

USE MASTER
GO
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'fn_HexToBinStr')
	DROP FUNCTION fn_HexToBinStr
GO
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'fn_HexToColList')
	DROP FUNCTION fn_HexToColList
GO
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'sp_userdefinedrolepermission' 
	   AND 	  type = 'P')
    DROP PROCEDURE sp_userdefinedrolepermission
GO
USE pubs
GO
IF EXISTS(SELECT name 
          FROM sysusers 
          WHERE name = 'TestMBProc')
      EXEC sp_droprole 'TestMBProc'
GO

*/------------------------------------------------------------

Edited by - PiecesOfEight on 10/11/2001 21:32:16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources