Retrieve server name and database name from Stored Procedure

Is there any way to retrieve

1. the server's computer name
2. the database's name

from a SQL Server Stored Procedure?

---

What I want to do is the following:

1. Run a Stored Procedure as user X
2. From the Stored Procedure, use OPENDATASOURCE to retrieve data that can only be retrieved as user Y. Unfortunately, Y cannot be hardcoded, because it is a parameter.

The database is meant to be deployed to several customers, and replicated by them as many times as they want to, etc. For this reason, I CANNOT HARDCODE the database server'开发者_开发技巧s name or the database's name.

(I know this smells like bad design. There is nothing I can do about it.)

---

1. SELECT SERVERPROPERTY('MachineName')
2. SELECT DB_NAME()

---

use SELECT db_name(); for the DB name and SELECT @@SERVERNAME; for server name

---

I'm not sure exactly what your goal is, but there are some intrinsics that might suit your need:

select DB_NAME(), @@SERVERNAME

You could wrap these in a procedure with out parameters if you chose, or use them in a regular query as needed.

One thing to be careful about is that @@SERVERNAME Is the name of the server, not necessarily the name of the physical computer name the server is hosted on. This becomes more important when you host multiple instances on a single machine or start moving apps into SqlAzure.

---

The computer name can be retrieved from the SERVERPROPERTY(property) function

create function getComputerName()
return varchar(128)
begin
    return select serverproperty('ServerName')
end

The existing DB_NAME() function returns the database name

create function getDatabaseName()
return varchar(128)
begin
   return db_name()
end

---

I actually think there's a better solution for you. Rather than finding out the server and database name, opening a new connection to that database with a new user, then querying, just to close the connection and revert to your original call, why not take advantage of SqlServer's "EXECUTE AS" clause:

CREATE USER me WITHOUT LOGIN
GO

CREATE PROCEDURE foo
    @UserToQueryAs SYSNAME
AS
BEGIN
    -- Do stuff as calling user

    EXECUTE AS USER = @UserToQueryAs;
    -- Do stuff as @UserToQueryAs
    SELECT USER_NAME();
    REVERT;

    -- Do more stuff as calling user
END
GO

exec foo 'me'

The above example results in: me