How can we help you today? How can we help you today?
Freda
Activity overview Badges (0) Posts (1) Comments (3)

Comments

Sort by recent activity
I have included the last two calls to sql server according to profiler. Both run okay when I run them on the individual databases through management studio. Thanks SELECT sp.grantee_principal_id as uid, su.name AS UserName, OBJECT_NAME(sp.major_id) AS ObjectName, SCHEMA_NAME(so.schema_id) AS ObjectOwner, 1 AS class, sp.type, sp.state, so.type AS ObjectType, CONVERT(VARBINARY(4000), null) as columns FROM sys.database_permissions AS sp WITH (NOLOCK) LEFT JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id=sp.major_id WHERE sp.major_id>0 AND sp.class = 1 AND sp.minor_id = 0 AND (sp.type in ('IN','DL','EX') OR CHARINDEX(sp.type, 'CRFNCRTBCRDBCRVWCRPRBADBCRDFBALOCRRU')%4 > 0) GROUP BY sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, sp.state, so.type, su.name, so.schema_id UNION ALL SELECT sp.grantee_principal_id AS uid, su.name AS UserName, OBJECT_NAME(sp.major_id) AS ObjectName, SCHEMA_NAME(so.schema_id) AS ObjectOwner, 1 AS class, sp.type, spParent.state, so.type AS ObjectType, CASE spParent.state WHEN 'R' THEN CONVERT(VARBINARY(4000), null) ELSE PowerSum(sp.minor_id) END AS columns FROM sys.database_permissions AS sp WITH (NOLOCK) INNER JOIN sys.database_permissions AS spParent ON spParent.class = 1 AND spParent.major_id=sp.major_id AND spParent.minor_id = 0 AND spParent.grantee_principal_id = sp.grantee_principal_id AND spParent.grantor_principal_id = sp.grantor_principal_id AND spParent.type = sp.type AND spParent.state <> 'R' LEFT JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id=sp.major_id where sp.major_id>0 AND sp.class = 1 AND sp.type IN ('RF', 'SL', 'UP','AL','CL','RC','VW','TO') GROUP BY sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, spParent.state, so.type, su.name, so.schema_id UNION ALL SELECT sp.grantee_principal_id AS uid, su.name AS UserName, OBJECT_NAME(sp.major_id) AS ObjectName, SCHEMA_NAME(so.schema_id) AS ObjectOwner, 1 AS class, sp.type, sp.state, so.type AS ObjectType, PowerSum(minor_id) AS columns FROM sys.database_permissions AS sp WITH (NOLOCK) LEFT JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id=sp.major_id WHERE sp.major_id>0 AND class = 1 AND minor_id <> 0 AND state <> 'R' GROUP BY sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, sp.state, so.type, su.name, so.schema_id UNION ALL SELECT sp.grantee_principal_id AS uid, su.name AS UserName, CASE sp.class WHEN 3 THEN (SELECT TOP 1 sch2.name FROM sys.schemas sch2 WITH (NOLOCK) WHERE sch2.schema_id = sp.major_id) WHEN 4 THEN (SELECT TOP 1 sp2.name FROM sys.database_principals sp2 WITH (NOLOCK) WHERE sp2.principal_id = sp.major_id) WHEN 5 THEN (SELECT TOP 1 sa2.name FROM sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id) WHEN 6 THEN (SELECT TOP 1 st2.name FROM sys.types st2 WITH (NOLOCK) WHERE st2.user_type_id = sp.major_id) WHEN 10 THEN (SELECT TOP 1 sx2.name FROM sys.xml_schema_collections sx2 WITH (NOLOCK) WHERE sx2.xml_collection_id = sp.major_id) WHEN 15 THEN (SELECT TOP 1 smt2.name FROM sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id) COLLATE database_default WHEN 16 THEN (SELECT TOP 1 ssc2.name FROM sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id) WHEN 17 THEN (SELECT TOP 1 ss2.name FROM sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id) WHEN 18 THEN (SELECT TOP 1 srs2.name FROM sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id) WHEN 19 THEN (SELECT TOP 1 sr2.name FROM sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id) WHEN 23 THEN (SELECT TOP 1 sft2.name FROM sys.fulltext_catalogs sft2 WITH (NOLOCK) WHERE sft2.fulltext_catalog_id = sp.major_id) WHEN 24 THEN (SELECT TOP 1 ssk2.name FROM sys.symmetric_keys ssk2 WITH (NOLOCK) WHERE ssk2.symmetric_key_id = sp.major_id) WHEN 26 THEN (SELECT TOP 1 sak2.name FROM sys.asymmetric_keys sak2 WITH (NOLOCK) WHERE sak2.asymmetric_key_id = sp.major_id) WHEN 25 THEN (SELECT TOP 1 sc2.name FROM sys.certificates sc2 WITH (NOLOCK) WHERE sc2.certificate_id = sp.major_id) ELSE N'' END AS ObjectName, CASE sp.class WHEN 10 THEN SCHEMA_NAME(( SELECT TOP 1 sx2.schema_id FROM sys.xml_schema_collections sx2 WITH (NOLOCK) WHERE sx2.xml_collection_id = sp.major_id)) WHEN 6 THEN SCHEMA_NAME((SELECT TOP 1 st2.schema_id FROM sys.types st2 WITH (NOLOCK) WHERE st2.user_type_id = sp.major_id)) ELSE USER_NAME(CASE sp.class WHEN 3 THEN (SELECT TOP 1 sch2.principal_id FROM sys.schemas sch2 WITH (NOLOCK) WHERE sch2.schema_id = sp.major_id) WHEN 4 THEN (SELECT TOP 1 sp2.owning_principal_id FROM sys.database_principals sp2 WITH (NOLOCK) WHERE sp2.principal_id = sp.major_id) WHEN 5 THEN (SELECT TOP 1 sa2.principal_id FROM sys.assemblies sa2 WITH (NOLOCK) WHERE sa2.assembly_id = sp.major_id) WHEN 15 THEN (SELECT TOP 1 smt2.principal_id FROM sys.service_message_types smt2 WITH (NOLOCK) WHERE smt2.message_type_id = sp.major_id) WHEN 16 THEN (SELECT TOP 1 ssc2.principal_id FROM sys.service_contracts ssc2 WITH (NOLOCK) WHERE ssc2.service_contract_id = sp.major_id) WHEN 17 THEN (SELECT TOP 1 ss2.principal_id FROM sys.services ss2 WITH (NOLOCK) WHERE ss2.service_id = sp.major_id) WHEN 18 THEN (SELECT TOP 1 srs2.principal_id FROM sys.remote_service_bindings srs2 WITH (NOLOCK) WHERE srs2.remote_service_binding_id = sp.major_id) WHEN 19 THEN (SELECT TOP 1 sr2.principal_id FROM sys.routes sr2 WITH (NOLOCK) WHERE sr2.route_id = sp.major_id) WHEN 23 THEN (SELECT TOP 1 sft2.principal_id FROM sys.fulltext_catalogs sft2 WITH (NOLOCK) WHERE sft2.fulltext_catalog_id = sp.major_id) WHEN 24 THEN (SELECT TOP 1 ssk2.principal_id FROM sys.symmetric_keys ssk2 WITH (NOLOCK) WHERE ssk2.symmetric_key_id = sp.major_id) WHEN 26 THEN (SELECT TOP 1 sak2.principal_id FROM sys.asymmetric_keys sak2 WITH (NOLOCK) WHERE sak2.asymmetric_key_id = sp.major_id) WHEN 25 THEN (SELECT TOP 1 sc2.principal_id FROM sys.certificates sc2 WITH (NOLOCK) WHERE sc2.certificate_id = sp.major_id) ELSE null END) END AS ObjectOwner, sp.class, sp.type, sp.state, CONVERT(CHAR(2), NULL) AS ObjectType, CONVERT(VARBINARY(4000), null) AS columns FROM sys.database_permissions AS sp WITH (NOLOCK) LEFT JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id WHERE major_id>=0 AND sp.class <> 1 SELECT CONVERT(bit, DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) AS FullTextEnabled [/code] / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Index was outside the bounds of the array
I have included the last two calls to sql server according to profiler. Both run okay when I run them on the individual databases through management studio. ThanksSELECT sp.grantee_principal_id a...
0 votes
We did check that and I have just confirmed it to make sure. It comes up with the same error when logging in with sa. Thanks / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Index was outside the bounds of the array
We did check that and I have just confirmed it to make sure. It comes up with the same error when logging in with sa. Thanks
0 votes
Hi Tried it with both versions - 5 and 5.1 Thanks / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Index was outside the bounds of the array
Hi Tried it with both versions - 5 and 5.1 Thanks
0 votes
Total activity
5
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
1