SSMS 2005 - Value does not fall within the expected range. (SqlMgm

Help, in SSMS 2005 I get the following error when I try to view the
securables on the Database Role Propery dialog.

If I click the Add button and on the Add Objects popup dialog select "Add
all object belonging to the schema..." then select the schema with the same
name as the Database Role the securables are shown.

Is the a fix to this problem?


===================================

Value does not fall within the expected range. (SqlMgmt)

------------------------------
Program Location:

at
Microsoft.SqlServer.Management.SqlMgmt.Permissions Data.Securable.Create(ObjectClass
objectClass, String objectType, String name, String schemaName, String
databaseName, Object connectionInfo, Version serverVersion)
at
Microsoft.SqlServer.Management.SqlMgmt.Permissions Data.Principal.AddRelatedSecurables(String urn, SecurableDictionary relatedSecurables)
at
Microsoft.SqlServer.Management.SqlMgmt.Permissions Data.Principal.AddExistingSecurables()
at
Microsoft.SqlServer.Management.SqlMgmt.Permissions SecurableSelector.OnHandleCreated(EventArgs e)
at System.Windows.Forms.Control.WmCreate(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Mes sage& m)
at System.Windows.Forms.ContainerControl.WndProc(Mess age& m)
at System.Windows.Forms.UUUUControl.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.O nMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)

If I REVOKE the permissions for the Database Role I can view the Securablies.

The redacted script for setting the permissions is:

ALTER PROCEDURE [MyDatabase].[SetRolePermission]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Print N'Set Role Permissions';
EXEC sys.sp_addrolemember @rolename=N'sql_dependency_subscriber',
@membername=N'Role';
-- =============================================
-- SCHEMA Restrictions
-- =============================================

-- =============================================
-- Role UA schema
-- =============================================
DENY ALTER ON SCHEMA::[UA] TO [Role]
DENY DELETE ON SCHEMA::[UA] TO [Role];
DENY TAKE Table9HIP ON SCHEMA::[UA] TO [Role];

-- =============================================
-- Role NS schema
-- =============================================
DENY ALTER ON SCHEMA::[NS] TO [Role];
DENY DELETE ON SCHEMA::[NS] TO [Role];
DENY TAKE Table9HIP ON SCHEMA::[NS] TO [Role];

-- =============================================
-- Role GX schema
-- =============================================
DENY ALTER ON SCHEMA::[GX] TO [Role];
DENY DELETE ON SCHEMA::[GX] TO [Role];
DENY TAKE Table9HIP ON SCHEMA::[GX] TO [Role];

-- =============================================
-- Role MS schema
-- =============================================
DENY ALTER ON SCHEMA::[MS] TO [Role];
DENY DELETE ON SCHEMA::[MS] TO [Role];
DENY TAKE Table9HIP ON SCHEMA::[MS] TO [Role];

-- =============================================
-- Role XA schema
-- =============================================
DENY ALTER ON SCHEMA::[XA] TO [Role];
DENY DELETE ON SCHEMA::[XA] TO [Role];
DENY TAKE Table9HIP ON SCHEMA::[XA] TO [Role];

-- =============================================
-- Role Vx schema
-- =============================================
DENY ALTER ON SCHEMA::[VX] TO [Role];
DENY DELETE ON SCHEMA::[VX] TO [Role];
DENY TAKE Table9HIP ON SCHEMA::[VX] TO [Role];

-- =============================================
-- QUERY Notification Permissions
-- =============================================
GRANT CREATE PROCEDURE TO [Role];
GRANT CREATE QUEUE TO [Role];
GRANT CREATE SERVICE TO [Role];
GRANT REFERENCES TO [Role];
GRANT SELECT TO [Role];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [Role];
GRANT RECEIVE ON QueryNotificationErrorsQueue to [Role];
GRANT VIEW DEFINITION TO [Role];
GRANT VIEW DATABASE STATE TO [Role];

-- =============================================
-- TABLES
-- =============================================

-- =============================================
-- XA
-- =============================================
GRANT REFERENCES ON [XA].[F_Table1] TO [Role];
GRANT SELECT ON [XA].[F_Table1] TO [Role];
GRANT VIEW DEFINITION ON [XA].[F_Table1] TO [Role];

GRANT REFERENCES ON [XA].[F_Table2] TO [Role];
GRANT SELECT ON [XA].[F_Table2] TO [Role];
GRANT VIEW DEFINITION ON [XA].[F_Table2] TO [Role];

GRANT REFERENCES ON [XA].[F_Table3] TO [Role];
GRANT SELECT ON [XA].[F_Table3] TO [Role];
GRANT VIEW DEFINITION ON [XA].[F_Table3] TO [Role];

GRANT REFERENCES ON [XA].[F_Table4] TO [Role];
GRANT SELECT ON [XA].[F_Table4] TO [Role];
GRANT VIEW DEFINITION ON [XA].[F_Table4] TO [Role];

-- =============================================
-- NS
-- =============================================
GRANT REFERENCES ON [NS].[M_Table5] TO [Role];
GRANT SELECT ON [NS].[M_Table5] TO [Role];
GRANT VIEW DEFINITION ON [NS].[M_Table5] TO [Role];

GRANT REFERENCES ON [NS].[M_Table6] TO [Role];
GRANT SELECT ON [NS].[M_Table6] TO [Role];
GRANT VIEW DEFINITION ON [NS].[M_Table6] TO [Role];

GRANT REFERENCES ON [NS].[M_Table7] TO [Role];
GRANT SELECT ON [NS].[M_Table7] TO [Role];
GRANT VIEW DEFINITION ON [NS].[M_Table7] TO [Role];

GRANT REFERENCES ON [NS].[M_Table8] TO [Role];
GRANT SELECT ON [NS].[M_Table8] TO [Role];
GRANT VIEW DEFINITION ON [NS].[M_Table8] TO [Role];

-- =============================================
-- UA
-- =============================================
GRANT REFERENCES ON [UA].[F_Table9] TO [Role];
GRANT SELECT ON [UA].[F_Table9] TO [Role];
GRANT VIEW DEFINITION ON [UA].[F_Table9] TO [Role];

GRANT REFERENCES ON [UA].[F_Table10] TO [Role];
GRANT SELECT ON [UA].[F_Table10] TO [Role];
GRANT VIEW DEFINITION ON [UA].[F_Table10] TO [Role];

GRANT REFERENCES ON [UA].[F_Table11] TO [Role];
--GRANT SELECT ON [UA].[F_Table11] TO [Role];
GRANT VIEW DEFINITION ON [UA].[F_Table11] TO [Role];

GRANT REFERENCES ON [UA].[F_Table12] TO [Role];
--GRANT SELECT ON [UA].[F_Table12] TO [Role];
GRANT VIEW DEFINITION ON [UA].[F_Table12] TO [Role];

GRANT REFERENCES ON [UA].[F_Table13] TO [Role];
--GRANT SELECT ON [UA].[F_Table13] TO [Role];
GRANT VIEW DEFINITION ON [UA].[F_Table13] TO [Role];

-- =============================================
-- Stored Procedures
-- XA
-- =============================================
GRANT EXECUTE ON [XA].[F_SP] TO [Role];
GRANT VIEW DEFINITION ON [XA].[F_SP] TO [Role];
-- Defined for each stored procedure need by Role.

-- =============================================
-- NS
-- =============================================
GRANT EXECUTE ON [NS].[M_SP] TO [Role];
GRANT VIEW DEFINITION ON [NS].[M_SP] TO [Role];
-- Defined for each stored procedure need by Role.

-- =============================================
-- UA
-- =============================================
GRANT EXECUTE ON [UA].[F_SP] TO [Role];
GRANT VIEW DEFINITION ON [UA].[F_SP] TO [Role];
-- Defined for each stored procedure need by Role.


EXEC sys.sp_helprotect @Rolename=N'Role'

END

Have you applied any service packs to your client tools? What does
Help|About say (should be either 9.00.1399 or 9.00.3042, unless you have SP1
which I don't remember the build # for SSMS).

I don't remember this bug specifically but it's possible that it existed in
RTM but was fixed in SP1 or SP2, in which case applying SP2 (and possibly
the latest cumulative update) will fix the problem...


On 8/22/08 1:27 PM, in article
DD1C7964-A602-47A6-8B66-72C964FCFD65-at-microsoft.com, "JM"
wrote:

> Help, in SSMS 2005 I get the following error when I try to view the
> securables on the Database Role Propery dialog.
>
> If I click the Add button and on the Add Objects popup dialog select "Add
> all object belonging to the schema..." then select the schema with the same
> name as the Database Role the securables are shown.
>
> Is the a fix to this problem?
>
>
> ===================================
>
> Value does not fall within the expected range. (SqlMgmt)
>
> ------------------------------
> Program Location:
>
> at
> Microsoft.SqlServer.Management.SqlMgmt.Permissions Data.Securable.Create(Object
> Class
> objectClass, String objectType, String name, String schemaName, String
> databaseName, Object connectionInfo, Version serverVersion)
> at
> Microsoft.SqlServer.Management.SqlMgmt.Permissions Data.Principal.AddRelatedSec
> urables(String urn, SecurableDictionary relatedSecurables)
> at
> Microsoft.SqlServer.Management.SqlMgmt.Permissions Data.Principal.AddExistingSe
> curables()
> at
> Microsoft.SqlServer.Management.SqlMgmt.Permissions SecurableSelector.OnHandleCr
> eated(EventArgs e)
> at System.Windows.Forms.Control.WmCreate(Message& m)
> at System.Windows.Forms.Control.WndProc(Message& m)
> at System.Windows.Forms.ScrollableControl.WndProc(Mes sage& m)
> at System.Windows.Forms.ContainerControl.WndProc(Mess age& m)
> at System.Windows.Forms.UUUUControl.WndProc(Message& m)
> at System.Windows.Forms.Control.ControlNativeWindow.O nMessage(Message& m)
> at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message& m)
> at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
> IntPtr wparam, IntPtr lparam)
>
> If I REVOKE the permissions for the Database Role I can view the Securablies.
>
> The redacted script for setting the permissions is:
>
> ALTER PROCEDURE [MyDatabase].[SetRolePermission]
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> Print N'Set Role Permissions';
> EXEC sys.sp_addrolemember @rolename=N'sql_dependency_subscriber',
> @membername=N'Role';
> -- =============================================
> -- SCHEMA Restrictions
> -- =============================================
>
> -- =============================================
> -- Role UA schema
> -- =============================================
> DENY ALTER ON SCHEMA::[UA] TO [Role]
> DENY DELETE ON SCHEMA::[UA] TO [Role];
> DENY TAKE Table9HIP ON SCHEMA::[UA] TO [Role];
>
> -- =============================================
> -- Role NS schema
> -- =============================================
> DENY ALTER ON SCHEMA::[NS] TO [Role];
> DENY DELETE ON SCHEMA::[NS] TO [Role];
> DENY TAKE Table9HIP ON SCHEMA::[NS] TO [Role];
>
> -- =============================================
> -- Role GX schema
> -- =============================================
> DENY ALTER ON SCHEMA::[GX] TO [Role];
> DENY DELETE ON SCHEMA::[GX] TO [Role];
> DENY TAKE Table9HIP ON SCHEMA::[GX] TO [Role];
>
> -- =============================================
> -- Role MS schema
> -- =============================================
> DENY ALTER ON SCHEMA::[MS] TO [Role];
> DENY DELETE ON SCHEMA::[MS] TO [Role];
> DENY TAKE Table9HIP ON SCHEMA::[MS] TO [Role];
>
> -- =============================================
> -- Role XA schema
> -- =============================================
> DENY ALTER ON SCHEMA::[XA] TO [Role];
> DENY DELETE ON SCHEMA::[XA] TO [Role];
> DENY TAKE Table9HIP ON SCHEMA::[XA] TO [Role];
>
> -- =============================================
> -- Role Vx schema
> -- =============================================
> DENY ALTER ON SCHEMA::[VX] TO [Role];
> DENY DELETE ON SCHEMA::[VX] TO [Role];
> DENY TAKE Table9HIP ON SCHEMA::[VX] TO [Role];
>
> -- =============================================
> -- QUERY Notification Permissions
> -- =============================================
> GRANT CREATE PROCEDURE TO [Role];
> GRANT CREATE QUEUE TO [Role];
> GRANT CREATE SERVICE TO [Role];
> GRANT REFERENCES TO [Role];
> GRANT SELECT TO [Role];
> GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [Role];
> GRANT RECEIVE ON QueryNotificationErrorsQueue to [Role];
> GRANT VIEW DEFINITION TO [Role];
> GRANT VIEW DATABASE STATE TO [Role];
>
> -- =============================================
> -- TABLES
> -- =============================================
>
> -- =============================================
> -- XA
> -- =============================================
> GRANT REFERENCES ON [XA].[F_Table1] TO [Role];
> GRANT SELECT ON [XA].[F_Table1] TO [Role];
> GRANT VIEW DEFINITION ON [XA].[F_Table1] TO [Role];
>
> GRANT REFERENCES ON [XA].[F_Table2] TO [Role];
> GRANT SELECT ON [XA].[F_Table2] TO [Role];
> GRANT VIEW DEFINITION ON [XA].[F_Table2] TO [Role];
>
> GRANT REFERENCES ON [XA].[F_Table3] TO [Role];
> GRANT SELECT ON [XA].[F_Table3] TO [Role];
> GRANT VIEW DEFINITION ON [XA].[F_Table3] TO [Role];
>
> GRANT REFERENCES ON [XA].[F_Table4] TO [Role];
> GRANT SELECT ON [XA].[F_Table4] TO [Role];
> GRANT VIEW DEFINITION ON [XA].[F_Table4] TO [Role];
>
> -- =============================================
> -- NS
> -- =============================================
> GRANT REFERENCES ON [NS].[M_Table5] TO [Role];
> GRANT SELECT ON [NS].[M_Table5] TO [Role];
> GRANT VIEW DEFINITION ON [NS].[M_Table5] TO [Role];
>
> GRANT REFERENCES ON [NS].[M_Table6] TO [Role];
> GRANT SELECT ON [NS].[M_Table6] TO [Role];
> GRANT VIEW DEFINITION ON [NS].[M_Table6] TO [Role];
>
> GRANT REFERENCES ON [NS].[M_Table7] TO [Role];
> GRANT SELECT ON [NS].[M_Table7] TO [Role];
> GRANT VIEW DEFINITION ON [NS].[M_Table7] TO [Role];
>
> GRANT REFERENCES ON [NS].[M_Table8] TO [Role];
> GRANT SELECT ON [NS].[M_Table8] TO [Role];
> GRANT VIEW DEFINITION ON [NS].[M_Table8] TO [Role];
>
> -- =============================================
> -- UA
> -- =============================================
> GRANT REFERENCES ON [UA].[F_Table9] TO [Role];
> GRANT SELECT ON [UA].[F_Table9] TO [Role];
> GRANT VIEW DEFINITION ON [UA].[F_Table9] TO [Role];
>
> GRANT REFERENCES ON [UA].[F_Table10] TO [Role];
> GRANT SELECT ON [UA].[F_Table10] TO [Role];
> GRANT VIEW DEFINITION ON [UA].[F_Table10] TO [Role];
>
> GRANT REFERENCES ON [UA].[F_Table11] TO [Role];
> --GRANT SELECT ON [UA].[F_Table11] TO [Role];
> GRANT VIEW DEFINITION ON [UA].[F_Table11] TO [Role];
>
> GRANT REFERENCES ON [UA].[F_Table12] TO [Role];
> --GRANT SELECT ON [UA].[F_Table12] TO [Role];
> GRANT VIEW DEFINITION ON [UA].[F_Table12] TO [Role];
>
> GRANT REFERENCES ON [UA].[F_Table13] TO [Role];
> --GRANT SELECT ON [UA].[F_Table13] TO [Role];
> GRANT VIEW DEFINITION ON [UA].[F_Table13] TO [Role];
>
> -- =============================================
> -- Stored Procedures
> -- XA
> -- =============================================
> GRANT EXECUTE ON [XA].[F_SP] TO [Role];
> GRANT VIEW DEFINITION ON [XA].[F_SP] TO [Role];
> -- Defined for each stored procedure need by Role.
>
> -- =============================================
> -- NS
> -- =============================================
> GRANT EXECUTE ON [NS].[M_SP] TO [Role];
> GRANT VIEW DEFINITION ON [NS].[M_SP] TO [Role];
> -- Defined for each stored procedure need by Role.
>
> -- =============================================
> -- UA
> -- =============================================
> GRANT EXECUTE ON [UA].[F_SP] TO [Role];
> GRANT VIEW DEFINITION ON [UA].[F_SP] TO [Role];
> -- Defined for each stored procedure need by Role.
>
>
> EXEC sys.sp_helprotect @Rolename=N'Role'
>
> END
>
>

Aaron,

First sorry for the delay in responding. I check the version and yes indeed
I needed SP2 installed. It took me a while to obtain a copy of SP2 and
install it.

The service pack fixed the problem.

Thanks