wkhazzard
Joined: 08 Mar 2011
Posts: 12
Location: Mid-Atlantic USA
|
 Posted: Wed Mar 09, 2011 7:57 pm Post subject: Exception When Registering a Database |
 |
|
I am getting an ArgumentOutOfRangeException when I register a database to create a snapshot. The code looks like this:
| Code: |
using (Database stagingDB = new Database())
{
var connectionProperties = new ConnectionProperties(
args[1], args[2]);
try
{
Console.WriteLine("Registering database " + connectionProperties.DatabaseName);
stagingDB.Register(connectionProperties,
Options.CaseSensitiveObjectDefinition | Options.DecryptPost2kEncryptedObjects);
// Save a snapshot of the database to WidgetStaging.snp
string snapshotFile = String.Format(
@"{0}\{1}-{2}.snp",
args[0], args[1].Replace('\\', '_'), args[2].Replace(' ', '_'));
Console.WriteLine("Saving snapshot file \"{0}\"...", snapshotFile);
stagingDB.SaveToDisk(snapshotFile);
}
catch (SqlException e)
{
Console.WriteLine("{0} occurred returning message '{1}'.",
e.GetType().Name, e.Message);
Console.WriteLine("Could not connect to database '{0}' on server '{1}'.",
connectionProperties.DatabaseName, connectionProperties.ServerName);
return -2;
}
return 0;
}
|
And here's the exception:
System.ArgumentOutOfRangeException was unhandled
Message=Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
Source=mscorlib
ParamName=index
StackTrace:
at System.Collections.BitArray.Set(Int32 index, Boolean value)
at System.Collections.BitArray.set_Item(Int32 index, Boolean value)
at RedGate.SQLCompare.Engine.DatabaseLevelPermissions.set_Item(DatabaseLevelPermissionAction permission, PermissionType value)
at o.H()
at o.F()
at o.a(n )
at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties connectionProperties, Options options)
at SaveDatabaseSnapshot.Program.Main(String[] args) in C:\Projects\Utilities\SaveDatabaseSnapshot\Program.cs:line 24
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
The exception is occuring in the .Register call. This code works fine one half a dozen other databases and I have no way to step into your code so I can't quite figure out why this one particular database is causing this error. Any ideas?
Thanks,
_________________
Kevin Hazzard, C# MVP & Friend of Red Gate
http://devjourney.com |
|
wkhazzard
Joined: 08 Mar 2011
Posts: 12
Location: Mid-Atlantic USA
|
| Posted: Sat Mar 12, 2011 6:11 pm Post subject: |
|
|
Hello James,
I ran the profiler as you recommended. Now I'm almost certain that it's the guest account is causing the issue. The last thing the shown in the profiler before the crash is a query that gets user and object permissions. I've attached it below along with two sets of results that show the successful "non-guest" access and the failing "guest access results. I've snipped the local users from the results in both cases to hide some details about my database from public eyes. But the most interesting part of the results is that none of the schemas have real data in them in the guest case. I suppose this is entirely my problem now. I must figure out how to remove the guest access from the database. The link you sent and the Microsoft help allude to disabling the guest login but I don't have a guest login showing in the server's security configuration. In a future version of the SDK's Database.Register method, it may be useful to determine if guest access has been enabled and report on that when exceptions occur.
Thanks,
Kevin
Here's the code that was executing and the two result sets:
| Code: |
SELECT CAST(0 AS INT) AS ColumnPermissionID,
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(int, null) as columns
FROM sys.database_permissions AS sp WITH (NOLOCK)
INNER 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)
AND so.schema_id IS NOT NULL
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
CAST((row_number() over (order by sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, spParent.state, so.type,
su.name, so.schema_id, sp.type, sp.minor_id)
- row_number() over (partition by sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, spParent.state, so.type,
su.name, so.schema_id, sp.type order by sp.minor_id)
+ 1 ) AS INT) AS ColumnPermissionID,
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(int, null)
ELSE
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'
INNER 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')
AND so.schema_id IS NOT NULL
UNION ALL
SELECT
CAST((row_number() over (partition by sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, so.type, sp.state order by sp.minor_id)
- row_number() over (order by sp.major_id, sp.grantee_principal_id, sp.grantor_principal_id, sp.type, so.type, sp.state, sp.minor_id)
- 1) AS INT) AS ColumnPermissionID,
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,
minor_id AS columns
FROM sys.database_permissions AS sp WITH (NOLOCK)
INNER 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'
AND so.schema_id IS NOT NULL
UNION ALL
SELECT
CAST(0 AS INT) AS ColumnPermissionID,
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(int, null) AS columns
FROM sys.database_permissions AS sp WITH (NOLOCK)
INNER JOIN sys.sysusers su WITH (NOLOCK) ON su.uid=sp.grantee_principal_id
WHERE major_id>=0 AND sp.class <> 1
|
/* successful "non-guest" results
ColumnPermissionID uid UserName ObjectName ObjectOwner class type state ObjectType columns
0 0 public sp_helpdiagrams dbo 1 EX G P NULL
0 2 guest sp_helpdiagrams dbo 1 EX D P NULL
0 0 public sp_helpdiagramdefinition dbo 1 EX G P NULL
0 2 guest sp_helpdiagramdefinition dbo 1 EX D P NULL
0 0 public sp_creatediagram dbo 1 EX G P NULL
0 2 guest sp_creatediagram dbo 1 EX D P NULL
0 0 public sp_renamediagram dbo 1 EX G P NULL
0 2 guest sp_renamediagram dbo 1 EX D P NULL
0 0 public sp_alterdiagram dbo 1 EX G P NULL
0 2 guest sp_alterdiagram dbo 1 EX D P NULL
0 0 public sp_dropdiagram dbo 1 EX G P NULL
0 2 guest sp_dropdiagram dbo 1 EX D P NULL
0 0 public fn_diagramobjects dbo 1 EX G FN NULL
0 2 guest fn_diagramobjects dbo 1 EX D FN NULL
0 1 dbo NULL 0 CO G NULL NULL
<snip/>
*/
/* errored "as guest" results
ColumnPermissionID uid UserName ObjectName ObjectOwner class type state ObjectType columns
0 0 public NULL 0 AL G NULL NULL
0 0 public NULL 0 ALAK G NULL NULL
0 0 public NULL 0 ALAR G NULL NULL
0 0 public NULL 0 ALAS G NULL NULL
0 0 public NULL 0 ALCF G NULL NULL
0 0 public NULL 0 ALDA G NULL NULL
0 0 public NULL 0 ALDS G NULL NULL
0 0 public NULL 0 ALED G NULL NULL
0 0 public NULL 0 ALFT G NULL NULL
0 0 public NULL 0 ALMT G NULL NULL
0 0 public NULL 0 ALRL G NULL NULL
0 0 public NULL 0 ALRT G NULL NULL
0 0 public NULL 0 ALSB G NULL NULL
0 0 public NULL 0 ALSC G NULL NULL
0 0 public NULL 0 ALSK G NULL NULL
0 0 public NULL 0 ALSM G NULL NULL
0 0 public NULL 0 ALSV G NULL NULL
0 0 public NULL 0 ALTG G NULL NULL
0 0 public NULL 0 ALUS G NULL NULL
0 0 public NULL 0 AUTH G NULL NULL
0 0 public NULL 0 BADB G NULL NULL
0 0 public NULL 0 BALO G NULL NULL
0 0 public NULL 0 CORP G NULL NULL
0 0 public NULL 0 CP G NULL NULL
0 1 dbo NULL 0 CO G NULL NULL
0 2 guest NULL 0 AL G NULL NULL
0 2 guest NULL 0 ALAK G NULL NULL
0 2 guest NULL 0 ALAR G NULL NULL
0 2 guest NULL 0 ALAS G NULL NULL
0 2 guest NULL 0 ALCF G NULL NULL
0 2 guest NULL 0 ALDA G NULL NULL
0 2 guest NULL 0 ALDS G NULL NULL
0 2 guest NULL 0 ALED G NULL NULL
0 2 guest NULL 0 ALFT G NULL NULL
0 2 guest NULL 0 ALMT G NULL NULL
0 2 guest NULL 0 ALRL G NULL NULL
0 2 guest NULL 0 ALRT G NULL NULL
0 2 guest NULL 0 ALSB G NULL NULL
0 2 guest NULL 0 ALSC G NULL NULL
0 2 guest NULL 0 ALSK G NULL NULL
0 2 guest NULL 0 ALSM G NULL NULL
0 2 guest NULL 0 ALSV G NULL NULL
0 2 guest NULL 0 ALTG G NULL NULL
0 2 guest NULL 0 ALUS G NULL NULL
0 2 guest NULL 0 AUTH G NULL NULL
0 2 guest NULL 0 BADB G NULL NULL
0 2 guest NULL 0 BALO G NULL NULL
0 2 guest NULL 0 CL G NULL NULL
0 2 guest NULL 0 CO G NULL NULL
0 2 guest NULL 0 CORP G NULL NULL
0 2 guest NULL 0 CP G NULL NULL
0 2 guest NULL 0 CRAG G NULL NULL
0 2 guest NULL 0 CRAK G NULL NULL
0 2 guest NULL 0 CRAS G NULL NULL
0 2 guest NULL 0 CRCF G NULL NULL
0 2 guest NULL 0 CRDF G NULL NULL
0 2 guest NULL 0 CRED G NULL NULL
0 2 guest NULL 0 CRFN G NULL NULL
0 2 guest NULL 0 CRFT G NULL NULL
0 2 guest NULL 0 CRMT G NULL NULL
0 2 guest NULL 0 CRPR G NULL NULL
0 2 guest NULL 0 CRQU G NULL NULL
0 2 guest NULL 0 CRRL G NULL NULL
0 2 guest NULL 0 CRRT G NULL NULL
0 2 guest NULL 0 CRRU G NULL NULL
0 2 guest NULL 0 CRSB G NULL NULL
0 2 guest NULL 0 CRSC G NULL NULL
0 2 guest NULL 0 CRSK G NULL NULL
0 2 guest NULL 0 CRSM G NULL NULL
0 2 guest NULL 0 CRSN G NULL NULL
0 2 guest NULL 0 CRSV G NULL NULL
0 2 guest NULL 0 CRTB G NULL NULL
0 2 guest NULL 0 CRTY G NULL NULL
0 2 guest NULL 0 CRVW G NULL NULL
0 2 guest NULL 0 CRXS G NULL NULL
0 2 guest NULL 0 DL G NULL NULL
0 2 guest NULL 0 EX G NULL NULL
0 2 guest NULL 0 IN G NULL NULL
0 2 guest NULL 0 RF G NULL NULL
0 2 guest NULL 0 SL G NULL NULL
0 2 guest NULL 0 SPLN G NULL NULL
0 2 guest NULL 0 SUQN G NULL NULL
0 2 guest NULL 0 TO G NULL NULL
0 2 guest NULL 0 UP G NULL NULL
0 2 guest NULL 0 VW G NULL NULL
0 2 guest NULL 0 VWDS G NULL NULL
<snip/>
*/ |
|
