Changing connection credentials

Hi,
I would like to change the credentials that are used at run time by the
reportserver service to connect to the SQL Server instance.
Can anybody please help me?
Regards!!Cookie =EB=FA=E1:
> Hi,
> I would like to change the credentials that are used at run time by the
> reportserver service to connect to the SQL Server instance.
> Can anybody please help me?
> Regards!!
http://support.microsoft.com/?id=3D306158
Impersonate a Specific User in Code
To impersonate a specific user only when you run a particular section
of code, use the following code:
<%@. Page Language=3D"C#"%>
<%@. Import Namespace =3D "System.Web" %>
<%@. Import Namespace =3D "System.Web.Security" %>
<%@. Import Namespace =3D "System.Security.Principal" %>
<%@. Import Namespace =3D "System.Runtime.InteropServices" %>
<script runat=3Dserver>
public const int LOGON32_LOGON_INTERACTIVE =3D 2;
public const int LOGON32_PROVIDER_DEFAULT =3D 0;
WindowsImpersonationContext impersonationContext;
[DllImport("advapi32.dll")]
public static extern int LogonUserA(String lpszUserName,
String lpszDomain,
String lpszPassword,
int dwLogonType,
int dwLogonProvider,
ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet=3DCharSet.Auto, SetLastError=3Dtrue)]
public static extern int DuplicateToken(IntPtr hToken,
int impersonationLevel,
ref IntPtr hNewToken);
[DllImport("advapi32.dll", CharSet=3DCharSet.Auto, SetLastError=3Dtrue)]
public static extern bool RevertToSelf();
[DllImport("kernel32.dll", CharSet=3DCharSet.Auto)]
public static extern bool CloseHandle(IntPtr handle);
public void Page_Load(Object s, EventArgs e)
{
if(impersonateValidUser("username", "domain", "password"))
{
//Insert your code that runs under the security context of a specific
user here.
undoImpersonation();
}
else
{
//Your impersonation failed. Therefore, include a fail-safe mechanism
here.
}
}
private bool impersonateValidUser(String userName, String domain,
String password)
{
WindowsIdentity tempWindowsIdentity;
IntPtr token =3D IntPtr.Zero;
IntPtr tokenDuplicate =3D IntPtr.Zero;
if(RevertToSelf())
{
if(LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,
LOGON32_PROVIDER_DEFAULT, ref token) !=3D 0)
{
if(DuplicateToken(token, 2, ref tokenDuplicate) !=3D 0)
{
tempWindowsIdentity =3D new WindowsIdentity(tokenDuplicate);
impersonationContext =3D tempWindowsIdentity.Impersonate();
if (impersonationContext !=3D null)
{
CloseHandle(token);
CloseHandle(tokenDuplicate);
return true;
}
}
}
}
if(token!=3D IntPtr.Zero)
CloseHandle(token);
if(tokenDuplicate!=3DIntPtr.Zero)
CloseHandle(tokenDuplicate);
return false;
}
private void undoImpersonation()
{
impersonationContext.Undo();
}
</script>

Changes not committed in SQL Server Project

I am brand new to vb.net and Visual Studio. I am following a series of lessons where I am supposed to be able to connect to a SQL Server Database bind the db to a form and then edit, add and delete records from within the form. When I run the project everything appears to work correctly, I can change the table data, add or delete rows but once I close the project and look at the underlying data, none of the changes are reflected. When I re-run the project, the data is just as it was before I made the changes.

I can edit the data directly in the tables and it works as you would expect. I'm sure that this is something bone-headed that I'm missing but I can't for the life of me find it.

Any guidance?

Thanks in advance for your help.

mpj

For .NET related questions, please see the Data Access forum in the .NET group.

Thank you.

|||

Will do...

Thanks

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in
a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.Change the server registration to use Windows Auth instead of SQL Auth?
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed i
n a
> while". Now nobody can get into the server via Enterprise Manager trying t
o
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without promptin
g
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed i
n a
> while". Now nobody can get into the server via Enterprise Manager trying t
o
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without promptin
g
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.Change the server registration to use Windows Auth instead of SQL Auth?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:
> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:
> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

Changed sa password - can't connect via Enterprise Manager now

A client of mine changed his SA password "because it hadn't been changed in a
while". Now nobody can get into the server via Enterprise Manager trying to
Connect on the server yields an error:
A connection could not be established to SERVER1.
Reason: login failed for user 'sa'.
Please verify SQL Server is running and check your SQL Server Registration
properties (by right-clicking on the SERVER1 node) and try again.
Of course, if I right-click on the SERVER1 node, I get the same message.
My client remembers the password he set; I managed to use it to get into
Query Analyzer, which prompts for a login each time it opens. But no luck
with Enterprise Manager. It's just trying to log in as sa without prompting
for a pw.
What can I do?
I already tried running sp_Password to change the pw back to what it was.
Still can't connect to the SQL Server via Enterprise Manager.
Change the server registration to use Windows Auth instead of SQL Auth?
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||Before connecting to server1 in EM, right click on it and edit the entry to
use the new password.
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||In SQLEM, right-click on the server registration, and Edit settings. In the
dialog, change the login information.
You should probably be using Windows Authentication and if you can not
because you are remoting, create another login that only you know the
password to and use that to login. No one should ever use the sa account
unless there is an emergency. Each user should have their own SQL Server
login created, or only Windows Authenticated, which would give each user
their own login--unless, of cours, you share your network logins too.
Sincerely,
Anthony Thomas
"Norrick" wrote:

> A client of mine changed his SA password "because it hadn't been changed in a
> while". Now nobody can get into the server via Enterprise Manager trying to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.
|||Sorry, you said "remembers" and somehow I read "doesn't remember"...
You can right-click the server, "Edit SQL Server Registration Properties..."
and change the password there.
http://www.aspfaq.com/
(Reverse address to reply.)
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:90915AE8-8D8E-4CC9-ABA3-0C7FC52A7BBA@.microsoft.com...
> A client of mine changed his SA password "because it hadn't been changed
in a
> while". Now nobody can get into the server via Enterprise Manager trying
to
> Connect on the server yields an error:
>
> A connection could not be established to SERVER1.
> Reason: login failed for user 'sa'.
> Please verify SQL Server is running and check your SQL Server Registration
> properties (by right-clicking on the SERVER1 node) and try again.
>
> Of course, if I right-click on the SERVER1 node, I get the same message.
> My client remembers the password he set; I managed to use it to get into
> Query Analyzer, which prompts for a login each time it opens. But no luck
> with Enterprise Manager. It's just trying to log in as sa without
prompting
> for a pw.
> What can I do?
> I already tried running sp_Password to change the pw back to what it was.
> Still can't connect to the SQL Server via Enterprise Manager.

changed listening port - cant connect Mangement Studio ?

Hi,
I have "SQL 2005 express edition" running on 2003 standard (R2).
I used the "SQL Server Configuration Manager" to changed the listening port
from 1433 to 1722.
I do this by following these instructions:
http://msdn2.microsoft.com/en-us/library/ms177440.aspx
Under "Protocols for MSSQLSERVER"
TCP/IP
IP1 = 1722
IP2 = 1722
IPALL = 1722
(dynamic ports are blank)
Under "SQL Native Client Configuration"
TCP/IP = 1722
Internally from an other machine i can run the following from a cmd prompt
"telnet 192.168.2.6 1722" and i get a connection.
When running "SQL Server Management Studio Express" however i now cannot
connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
the following:
"Cannot connect ... an error has occurred ... maybe caused by the fact
that under default settings SQL Server does not allow remote connection".
This is a incorrect as it works on 1433 fine. (also tried connection
Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
but no banana.
Thank for any help
Scottscott wrote:
> Hi,
> I have "SQL 2005 express edition" running on 2003 standard (R2).
> I used the "SQL Server Configuration Manager" to changed the listening por
t
> from 1433 to 1722.
> I do this by following these instructions:
> http://msdn2.microsoft.com/en-us/library/ms177440.aspx
> Under "Protocols for MSSQLSERVER"
> TCP/IP
> IP1 = 1722
> IP2 = 1722
> IPALL = 1722
> (dynamic ports are blank)
> Under "SQL Native Client Configuration"
> TCP/IP = 1722
> Internally from an other machine i can run the following from a cmd prompt
> "telnet 192.168.2.6 1722" and i get a connection.
> When running "SQL Server Management Studio Express" however i now cannot
> connect. I enter 192.168.2.6:1722 as the connection IP and it times out wi
th
> the following:
> "Cannot connect ... an error has occurred ... maybe caused by the fact
> that under default settings SQL Server does not allow remote connection".
> This is a incorrect as it works on 1433 fine. (also tried connection
> Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
> but no banana.
> Thank for any help
> Scott
>
Use a comma in Management Studio, like this: 192.168.2.6,1722
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||perfect, thanks
Scott

changed listening port - cant connect Mangement Studio ?

Hi,
I have "SQL 2005 express edition" running on 2003 standard (R2).
I used the "SQL Server Configuration Manager" to changed the listening port
from 1433 to 1722.
I do this by following these instructions:
http://msdn2.microsoft.com/en-us/library/ms177440.aspx
Under "Protocols for MSSQLSERVER"
TCP/IP
IP1 = 1722
IP2 = 1722
IPALL = 1722
(dynamic ports are blank)
Under "SQL Native Client Configuration"
TCP/IP = 1722
Internally from an other machine i can run the following from a cmd prompt
"telnet 192.168.2.6 1722" and i get a connection.
When running "SQL Server Management Studio Express" however i now cannot
connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
the following:
"Cannot connect ... an error has occurred ... maybe caused by the fact
that under default settings SQL Server does not allow remote connection".
This is a incorrect as it works on 1433 fine. (also tried connection
Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
but no banana.
Thank for any help
Scottscott wrote:
> Hi,
> I have "SQL 2005 express edition" running on 2003 standard (R2).
> I used the "SQL Server Configuration Manager" to changed the listening port
> from 1433 to 1722.
> I do this by following these instructions:
> http://msdn2.microsoft.com/en-us/library/ms177440.aspx
> Under "Protocols for MSSQLSERVER"
> TCP/IP
> IP1 = 1722
> IP2 = 1722
> IPALL = 1722
> (dynamic ports are blank)
> Under "SQL Native Client Configuration"
> TCP/IP = 1722
> Internally from an other machine i can run the following from a cmd prompt
> "telnet 192.168.2.6 1722" and i get a connection.
> When running "SQL Server Management Studio Express" however i now cannot
> connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
> the following:
> "Cannot connect ... an error has occurred ... maybe caused by the fact
> that under default settings SQL Server does not allow remote connection".
> This is a incorrect as it works on 1433 fine. (also tried connection
> Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
> but no banana.
> Thank for any help
> Scott
>
Use a comma in Management Studio, like this: 192.168.2.6,1722
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||perfect, thanks
Scott

change ssis connection.

Hi,

i have SSIS proj whice now it connect to server A (whice is our test server)

now i want to modified our project that when we want it'll connect to server B (Our Prod server).

now i'm looking for something global that will switch the connection strings whenever i want.

what i find so far is to declare var for each package and give the var default value and to create script to change the value of the var.

is there a solution that will be for all the project and i don't have to change each package (i have alot of pckgs).

Thanks in advanced.

Configurations, as referenced in your other posts.

Change Login-Info for Activity-Monitor and sp_who

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas

DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.

Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.

Thomas

You will see only Thomas and AppUser that are used by web server to connect to server and use objects of db.

I think in your db there is a table that contain users with encrypted passwords and if you want to see who access your application when the user app try to connect with or without succes , using sp that making the verification you can put somehere in a table the information that the user is connected. Querying that table you can see who is connected.

|||

Since Login to User is 1 to M , you will not get the result as you mentioned in either SP_Who or Activity monitor. You can make a customized sp from sp_who and you can use that. Just you need to join with sys.database_principals

(a) SP_Helptext sp_Who -- Will give u the source code of sp_who

(b) Join with sys.database_principals .

the following script is taken from sp_who and then joined with sys.database_principals . sys.database_principals is database specific. So you should run this script in the particular database to get the desired result.

select spid , ecid, status

,loginame=rtrim(loginame)

,hostname ,blk=convert(char(5),blocked)

,dbname = case

when dbid = 0 then null

when dbid <> 0 then db_name(dbid)

end

,cmd

,request_id ,sdp.Name as UserName

from master.dbo.sysprocesses inner join

sys.database_principals sdp on sdp.sid=master.dbo.sysprocesses .sid

Madhu

Change Login-Info for Activity-Monitor and sp_who

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas

DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.

Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.

Thomas

You will see only Thomas and AppUser that are used by web server to connect to server and use objects of db.

I think in your db there is a table that contain users with encrypted passwords and if you want to see who access your application when the user app try to connect with or without succes , using sp that making the verification you can put somehere in a table the information that the user is connected. Querying that table you can see who is connected.

|||

Since Login to User is 1 to M , you will not get the result as you mentioned in either SP_Who or Activity monitor. You can make a customized sp from sp_who and you can use that. Just you need to join with sys.database_principals

(a) SP_Helptext sp_Who -- Will give u the source code of sp_who

(b) Join with sys.database_principals .

the following script is taken from sp_who and then joined with sys.database_principals . sys.database_principals is database specific. So you should run this script in the particular database to get the desired result.

select spid , ecid, status

,loginame=rtrim(loginame)

,hostname ,blk=convert(char(5),blocked)

,dbname = case

when dbid = 0 then null

when dbid <> 0 then db_name(dbid)

end

,cmd

,request_id ,sdp.Name as UserName

from master.dbo.sysprocesses inner join

sys.database_principals sdp on sdp.sid=master.dbo.sysprocesses .sid

Madhu