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?

2
  • 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. Commented Aug 1, 2017 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. Commented Aug 1, 2017 at 13:52

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.