Getting around in the mssql environment


In MSSQL (2000) there’s not an easy way to see in which database you are. Script below creates a view ‘whoami’ that gives you the information;

create view dbo.whoami
select @@spid as '@@spid',
       cast( user as varchar(12)) as 'user',
       cast( p.dbid as varchar(6)) + ':' + cast( d.name as varchar(14)) as 'database',
       cast( p.loginame as varchar(16)) loginame,
       cast( p.hostname as varchar(16)) as 'hostname',
       cast( p.nt_username as varchar(16)) nt_username
   from master.dbo.sysprocesses p left join master.dbo.sysdatabases d
      on d.dbid = p.dbid
   where spid = @@spid


grant select on dbo.whoami to public
1> select * from whoami
2> go
 @@spid user         database              loginame         hostname         nt_username
 ------ ------------ --------------------- ---------------- ---------------- ----------------
     51 dbo          1:master              NYC01\\remivisser NYC01            remivisser

(1 row affected)

Leave a Reply

Your email address will not be published. Required fields are marked *