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:
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:
How do I include the synonyms in my script?
