Database mail availability

I wanted to know which version of SQL Server 2005 supports Database Mail. I have a SQL Server 2005 Standard Edition. Does that version have the full functionality of database mail.Yes, Database Mail is available in Standard Edition.

Rob

Database Logon problem Crystal Report

I am using following code in form load.
it works fine on one PC but when i run the same application on other pc with different version of Windows Xp it is asking for database logon for report.

I did tried using my provided login info using code but it is executing front end query as well as query in the report.

Any Suggestions will be appreciated.
Thanks in advance



ConnStr = "PROVIDER=MSDAORA.1;PERSIST SECURITY INFO=FALSE;USER ID=scott;PASSWORD=tiger;DATA SOURCE=orcl"
conObj = New OleDbConnection(ConnStr)

strsql = "select * from emp"

cmdObj.Connection = conObj

cmdObj.CommandText = strsql

daObj.SelectCommand = cmdObj

daObj.Fill(dsObj, "test")

rptDoc.Load("\\server\reports\abc.rpt")

srcCr = rptDoc

srcCr.SetDataSource(dsObj)

CRV.ReportSource = rptDoc

CRV.Show()i used CR viewer of CR XI ,it solved the problem. other system had cr viewer 9.5

Database List Hardcoded into SQL Server Management

Hi,
i'm working on the rtm version of mssql 2005 and i'm wondering to find that the database list are hard coded into SQL Server Management.
I need to hide databases list for (newbie) users on EM 2000 i can hide this list using this kb http://support.microsoft.com/?id=889696 but on the new SQLSM

The database list is hard coded into this batch query

SELECT

dtb.name AS [Database_Name],

'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(dtb.name,'''') + ']'

AS [Database_Urn],

case

-- if all these are false then we are in the Normal state

-- except some return NULL if it's AutoClosed

when (DATABASEPROPERTY(dtb.name,'IsInLoad') = 0 and

(DATABASEPROPERTY(dtb.name,'IsInRecovery') = 0 or DATABASEPROPERTY(dtb.name,'IsInRecovery') is null) and

(DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 0 or DATABASEPROPERTY(dtb.name,'IsNotRecovered') is null) and

DATABASEPROPERTY(dtb.name,'IsSuspect') = 0 and

DATABASEPROPERTY(dtb.name,'IsOffline') = 0 and

DATABASEPROPERTY(dtb.name,'IsInStandBy') = 0 and

(DATABASEPROPERTY(dtb.name,'IsShutDown') = 0 or DATABASEPROPERTY(dtb.name,'IsShutDown') is null) and

DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 0) then 1

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsInLoad') = 1 then 2

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 and

DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 1 then 4

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 then 8

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsSuspect') = 1 then 16

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsOffline') = 1 then 32

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsInStandBy') = 1 then 64

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsShutDown') = 1 then 128

when DATABASEPROPERTY(dtb.name,'IsShutDown') is null then (512 + 128)

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 1 then 256

else 0

end

AS [Database_Status],

dtb.cmptlevel AS [Database_CompatibilityLevel],

CASE DATABASEPROPERTYEX(dtb.name, 'Recovery') WHEN 'SIMPLE' THEN 3 WHEN 'BULK_LOGGED' THEN 2 ELSE /*FULL*/ 1 END AS [RecoveryModel],

CASE CONVERT(sysname,DATABASEPROPERTYEX(dtb.name, 'UserAccess')) WHEN 'SINGLE_USER' THEN 1 WHEN 'RESTRICTED_USER' THEN 2 ELSE /*MULTI_USER*/

0 END AS [UserAccess],

CAST(DATABASEPROPERTY(dtb.name, 'IsReadOnly') AS bit) AS [ReadOnly],

dtb.name AS [Database_DatabaseName2]

FROM

master.dbo.sysdatabases AS dtb

WHERE

(CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else category & 16 end AS bit)=0)

ORDER BY

[Database_Name] ASC

So my question it's possible to have a stored procedure on the next fix of mssql 2005 with a stored procedure on the master database to show the database list, i know that the database are listed on the table master.dbo.sysdatabases but i need to hide database for newbie users.

If your question is related to Management Studio, please post to SQL Server Tools General.

If you don't want a user to be able to see all entries of sys.databases/sysdatabases, then you can just deny VIEW ANY DATABASE permission to them. By default, this permission is assigned to public (for backward compatibility with SQL Server 2000), so every principal has it.

Thanks
Laurentiu|||Hi Laurentiu,
yes the question is related to Management Studio and i'll post on the correct forum.
Thank's|||

Note: with SP2 client, DENY VIEW ANY DATABASE TO <login> results in NO, NONE, NATA databases other than master and tempdb being visible in object explorer. The problem is clearly that SSMS calls master.dbo.databases - if it would just call dbo.databases (using the login's default database) then all the databases the user has access to might be listed. I will check the other forum for help, but MS PM's- if you're listening- this is terribly annoying.

Yes, I have tried adding the logins as users in master, then granting select on the system view- deny view any database permission trumps it. Also, why is there no [GRANT/REVOKE/DENY] VIEW DATABASE::<dbname> syntax?!!!

Other than permissions, and their quirks such as this, SQL Server is a great product.

Database List Hardcoded into SQL Server Management

Hi,
i'm working on the rtm version of mssql 2005 and i'm wondering to find that the database list are hard coded into SQL Server Management.
I need to hide databases list for (newbie) users on EM 2000 i can hide this list using this kb http://support.microsoft.com/?id=889696 but on the new SQLSM

The database list is hard coded into this batch query

SELECT

dtb.name AS [Database_Name],

'Server[@.Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@.Name=' + quotename(dtb.name,'''') + ']'

AS [Database_Urn],

case

-- if all these are false then we are in the Normal state

-- except some return NULL if it's AutoClosed

when (DATABASEPROPERTY(dtb.name,'IsInLoad') = 0 and

(DATABASEPROPERTY(dtb.name,'IsInRecovery') = 0 or DATABASEPROPERTY(dtb.name,'IsInRecovery') is null) and

(DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 0 or DATABASEPROPERTY(dtb.name,'IsNotRecovered') is null) and

DATABASEPROPERTY(dtb.name,'IsSuspect') = 0 and

DATABASEPROPERTY(dtb.name,'IsOffline') = 0 and

DATABASEPROPERTY(dtb.name,'IsInStandBy') = 0 and

(DATABASEPROPERTY(dtb.name,'IsShutDown') = 0 or DATABASEPROPERTY(dtb.name,'IsShutDown') is null) and

DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 0) then 1

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsInLoad') = 1 then 2

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 and

DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 1 then 4

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 then 8

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsSuspect') = 1 then 16

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsOffline') = 1 then 32

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsInStandBy') = 1 then 64

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsShutDown') = 1 then 128

when DATABASEPROPERTY(dtb.name,'IsShutDown') is null then (512 + 128)

else 0

end |

case

when DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 1 then 256

else 0

end

AS [Database_Status],

dtb.cmptlevel AS [Database_CompatibilityLevel],

CASE DATABASEPROPERTYEX(dtb.name, 'Recovery') WHEN 'SIMPLE' THEN 3 WHEN 'BULK_LOGGED' THEN 2 ELSE /*FULL*/ 1 END AS [RecoveryModel],

CASE CONVERT(sysname,DATABASEPROPERTYEX(dtb.name, 'UserAccess')) WHEN 'SINGLE_USER' THEN 1 WHEN 'RESTRICTED_USER' THEN 2 ELSE /*MULTI_USER*/

0 END AS [UserAccess],

CAST(DATABASEPROPERTY(dtb.name, 'IsReadOnly') AS bit) AS [ReadOnly],

dtb.name AS [Database_DatabaseName2]

FROM

master.dbo.sysdatabases AS dtb

WHERE

(CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else category & 16 end AS bit)=0)

ORDER BY

[Database_Name] ASC

So my question it's possible to have a stored procedure on the next fix of mssql 2005 with a stored procedure on the master database to show the database list, i know that the database are listed on the table master.dbo.sysdatabases but i need to hide database for newbie users.

If your question is related to Management Studio, please post to SQL Server Tools General.

If you don't want a user to be able to see all entries of sys.databases/sysdatabases, then you can just deny VIEW ANY DATABASE permission to them. By default, this permission is assigned to public (for backward compatibility with SQL Server 2000), so every principal has it.

Thanks
Laurentiu|||Hi Laurentiu,
yes the question is related to Management Studio and i'll post on the correct forum.
Thank's|||

Note: with SP2 client, DENY VIEW ANY DATABASE TO <login> results in NO, NONE, NATA databases other than master and tempdb being visible in object explorer. The problem is clearly that SSMS calls master.dbo.databases - if it would just call dbo.databases (using the login's default database) then all the databases the user has access to might be listed. I will check the other forum for help, but MS PM's- if you're listening- this is terribly annoying.

Yes, I have tried adding the logins as users in master, then granting select on the system view- deny view any database permission trumps it. Also, why is there no [GRANT/REVOKE/DENY] VIEW DATABASE::<dbname> syntax?!!!

Other than permissions, and their quirks such as this, SQL Server is a great product.