Monday, January 4, 2010

Permissions issue in SSMS

Related stackoverflow question:
Permissions issue in SSMS: “The SELECT permission was denied on the object ‘extended_properties’, database ‘mssqlsystem_resource’, … Error 229)”
Here is the specific portion of the larger command that is causing the permissions issue:
SELECT major_id
FROM sys.extended_properties

Below is the complete command that causes the error. This was pulled from a trace. The user was robg_test.

EXEC sp_executesql N'
        DECLARE @is_policy_automation_enabled bit
        SET @is_policy_automation_enabled  = (SELECT CONVERT(bit, current_value)
                                              FROM msdb.dbo.syspolicy_configuration
                                              WHERE name = ''Enabled'')
SELECT
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/Table[@Name='' + quotename(tbl.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(tbl.schema_id),'''''''') + '']'' AS [Urn],
tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
CAST(
 case
    when tbl.is_ms_shipped = 1 then 1
    when (
        select
            major_id
        from
            sys.extended_properties
        where
            major_id = tbl.object_id and
            minor_id = 0 and
            class = 1 and
            name = N''microsoft_database_tools_support'')
        is not null then 1
    else 0
end   
             AS bit) AS [IsSystemObject],
tbl.create_date AS [CreateDate],
stbl.name AS [Owner],
case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like ''Server'' + ''/Database\[@ID='' + convert(nvarchar(20),dtb.database_id) + ''\]''+ ''/Table\[@ID='' + convert(nvarchar(20),tbl.object_id) + ''\]%'' ESCAPE ''\'') then 1 else 0 end AS [PolicyHealthState]
FROM
master.sys.databases AS dtb,
sys.tables AS tbl
INNER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, ''OwnerId'')))
WHERE
(CAST(
 case
    when tbl.is_ms_shipped = 1 then 1
    when (
        select
            major_id
        from
            sys.extended_properties
        where
            major_id = tbl.object_id and
            minor_id = 0 and
            class = 1 and
            name = N''microsoft_database_tools_support'')
        is not null then 1
    else 0
end   
             AS bit)=@_msparam_0)and((db_name()=@_msparam_1)and(dtb.name=db_name()))
ORDER BY
[Schema] ASC,[Name] ASC'
,N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'RGTest'

No comments:

Post a Comment