1

I have a script that gives me all the permissions assigned to a database object:

-------------------------------------------------
-- permissions to database object
-- marcelo miorelli v. 20170401
-------------------------------------------------

declare @dbname sysname  = 'mydatabase'
       ,@ObjName sysname = 'thetable1'

SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY NORMAL;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


    DECLARE @sql NVARCHAR(MAX)
    DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @retval INT  

     /*Use QUOTENAME to correctly escape any special characters*/
    SET @sql = N'USE '+ QUOTENAME(@dbname) + N'

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

;WITH
RADHARANI AS (

                SELECT
                    dp.NAME AS principal_name
                        ,dp.type_desc AS principal_type_desc
                        ,o.NAME AS object_name
                        ,o.type_desc
                        ,p.permission_name
                        ,p.state_desc AS permission_state_desc
                    FROM sys.all_objects  o

              INNER JOIN sys.database_permissions  p 
                         ON o.OBJECT_ID=p.major_id

         LEFT OUTER JOIN sys.database_principals  dp 
                         ON p.grantee_principal_id = dp.principal_id

                   WHERE 1=1
                     AND O.OBJECT_ID > 0
                     AND O.TYPE <> ''S''    -- no system
                     AND O.parent_object_id = 0 -- no constraints
                     AND o.NAME like @ObjName

               UNION ALL

                SELECT
                    dp.NAME AS principal_name
                        ,dp.type_desc AS principal_type_desc
                        ,o.NAME AS object_name
                        ,[type_desc] = ''User-Defined Table Type''
                        ,p.permission_name
                        ,p.state_desc AS permission_state_desc

                    FROM sys.table_types  o

              INNER JOIN sys.database_permissions  p 
                      ON o.user_type_id=p.major_id

         LEFT OUTER JOIN sys.database_principals  dp 
                      ON p.grantee_principal_id = dp.principal_id

                   WHERE o.NAME like @ObjName

)


SELECT * FROM RADHARANI
SELECT @retvalOUT = @@ROWCOUNT
'

SET @ParamDefinition = N'@retvalOUT int OUTPUT, 
                         @ObjName sysname';

BEGIN TRY
EXEC sp_executesql @SQL, 
                   @ParamDefinition, 
                   @retvalOUT= @retval OUTPUT, 
                   @ObjName  = @ObjName;

IF ( @retval = 0 )
    BEGIN

        PRINT '------------------------------'
        PRINT 'The Object ' + @ObjName + 
              ' does not have any permissions assigned to it.' 
        PRINT '------------------------------'


    END--IF

This worked so fine, and I loved it, however, today I got a developer telling me about some problems, not being able to update a table.

this is not working:

UPDATE tbl_ORCAStatusChanges
SET messageStatus        = 1
WHERE     applicationID    = @applicationID
    AND messageStatus    = 0

But when have a look at the permissions for the table tbl_ORCAStatusChanges all seems to be fine because the user in question has the update permission:

enter image description here

I see it is not a table within my database, but a synonym.

this is how I found out at what other object this synonym is pointing to:

use Mydatabase
go

SELECT 
    the_schema= SCHEMA_NAME(s.schema_id)
   ,the_object=s.name
   ,the_type =s.type_desc
   ,s.base_object_name,
  the_DB_ID= DB_ID(PARSENAME(base_object_name,3)),
  the_DB_name=DB_NAME(DB_ID(PARSENAME(base_object_name,3)))
FROM sys.synonyms s
WHERE 1=1
  AND s.name LIKE 'tbl_ORCAStatusChanges'

to which I get:

enter image description here

How do I include the synonyms in my script?

  • Is there a reason you can't simply run an existence check against your synonyms query after your initial declare(s) of dbname and ObjName variables. If you get a row back, overlay your dbname and ObjName variables with the results of the synonym check. I would think the rest of your script would just work. – Scott Hodgin Aug 1 '17 at 13:02
  • yes, I guess that would be a simple way to get it done. get the sinonyms first if they exist and union all with the ObjName, including the dbName in the return. – Marcello Miorelli Aug 1 '17 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.