 | |
SSMS 2005 - Value does not fall within the expected range. (SqlMgm
microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
SSMS 2005 - Value does not fall within the expected range. (SqlMgm -
08-22-2008
, 12:27 PM
#2
| |||
| |||
|
|
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 |
#3
| |||
| |||
|
|
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 |
#4
| |||
| |||
|
|
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 |
#5
| |||
| |||
|
|
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 |
#6
| |||
| |||
|
|
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 |
#7
| |||
| |||
|
|
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 |
#8
| |||
| |||
|
|
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 |
#9
| |||
| |||
|
|
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 |
#10
| |||
| |||
|
|
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 |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.