Database.EnumObjectPermissions throws InvalidCastException

Hi,

I am trying to run the EnumObjectPermissions method on the pubs database in SQL Server 2000. When it tries to enumerate the permissions for the public role it throws a InvalidCastException saying that you cannot cast a System.DBNull to System.String

I know that its not my code so I am unsure what to do to fix this issue. The code runs fine against other databases.

James

This may be a SMO bug. Do you have a code sample for this?|||

I'm able to reproduce the InvalidCastException error on any SQL Server 2000 database by granting a "CREATE" database level permissions. By default the pubs database has these types of permissions granted to guest and public. I ran a trace an noticed SMO is sending the following query for EnumObjectPermissions calls:

SELECT
grantee_principal.name AS [Grantee],
grantor_principal.name AS [Grantor],
CASE prmssn.protecttype WHEN 204 THEN 87 WHEN 205 THEN 71 WHEN 206 THEN 68 END AS [PermissionState],
null AS [Code],
CASE prmssn.id WHEN 0 THEN 0 ELSE 1 END AS [ObjectClass],
null AS [ColumnName],
object_name(prmssn.id) AS [ObjectName],
user_name(prmssn.uid) AS [ObjectSchema],
prmssn.id AS [ObjectID],
CASE when 1 = grantee_principal.issqlrole THEN 3 WHEN 1 = grantee_principal.isapprole THEN 4 ELSE 2 END AS [GranteeType],
CASE when 1 = grantor_principal.issqlrole THEN 3 WHEN 1 = grantor_principal.isapprole THEN 4 ELSE 2 END AS [GrantorType],
CAST(prmssn.action AS int) AS [SqlCodePP]
FROM
dbo.sysprotects AS prmssn
INNER JOIN sysusers AS grantee_principal ON grantee_principal.uid = prmssn.uid
INNER JOIN sysusers AS grantor_principal ON grantor_principal.uid = prmssn.grantor

The ObjectName column is NULL for database level permissions, I would think EnumObjectPermissions would exclude database level permissions which should be included in EnumDatrabasePermissions?

Here's the SMO code I'm running against a SQL Server 2000 instance:

ServerConnection conn = new ServerConnection();

conn.LoginSecure = true;
conn.ServerInstance = "MyServer";

Server srv = new Server(conn);

foreach (Database db in srv.Databases)
{
Console.WriteLine(db.Name);

foreach (ObjectPermissionInfo objPermInfo in db.EnumObjectPermissions())
{
Console.WriteLine("{0,-35} {1,-25}", objPermInfo.ObjectName, objPermInfo.PermissionType);
}

}

|||I opened a case on this issue and it has been logged as a bug. The fix may be included in SQL 2005 SP2 or may be released as a hotfix