WhoAmI database view

Sometimes it can be useful to be able to know the result of a database connection.
This can be done by creating the view [dbo].[WhoAmI] in most or each database. The view can be defined as
@@SERVERNAME AS 'ServerName'
,@@SERVICENAME AS 'InstanceName'
,@@VERSION AS 'SqlVersion'
,DB_NAME() AS 'DatabaseName'
,SUSER_SNAME() AS 'UserLogin'
,HOST_NAME() AS 'ClientName'
,APP_NAME() AS 'ApplicationName'
,ORIGINAL_LOGIN() AS 'OriginalLogin'
,USER_NAME() AS 'DatabaseUserName'

And then grant SELECT to the database role [public] by
GRANT SELECT ON [dbo].[WhoAmI] TO [public];
Normally I would ban the use of the database role [public], but this is an usage I could accept.

Such a view was very useful debugging a system using Oracle on LINUX, SQL Server and Oracle Transparant Gateway (TG). We had several connectivity problems, and were glad to be able to find out which database and in which context we actually got the connection.

If you use extended properties on your databases, you could include these in the view by using the function fn_listextendedproperty().

No comments: