September 10, 2009 at 1:10 am
I am executing below script in SSMS
EXEC master.dbo.xp_cmdshell 'SET'
strangely it shows all the values except that what i want...
USERDNSDOMAIN
USERDOMAIN
USERNAME
but when i run the same in command prompt i.e
1. open cmd prompt
2. type 'set' and hit ENTER
I get all the values including the above three.....
can someone help me why this is so....?
I have tried to run the same script is different domains and i got the same problem.... Now i am not able to determine whether SQL with never show these values or this is happening only for me because i am in a restricted domains.
Thanks
Naresh
September 10, 2009 at 1:22 am
naresh1253 (9/10/2009)
I am executing below script in SSMSEXEC master.dbo.xp_cmdshell 'SET'
strangely it shows all the values except that what i want...
USERDNSDOMAIN
USERDOMAIN
USERNAME
but when i run the same in command prompt i.e
1. open cmd prompt
2. type 'set' and hit ENTER
I get all the values including the above three.....
can someone help me why this is so....?
I have tried to run the same script is different domains and i got the same problem.... Now i am not able to determine whether SQL with never show these values or this is happening only for me because i am in a restricted domains.
Thanks
Naresh
Which login are you using when you run this. might be down to the fact that it isnt using your login.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 10, 2009 at 2:21 am
I am using windows login...also i am admin on the machine.....I tried with sa as well....
September 10, 2009 at 2:51 am
I think you will find that, depending on the account you are running it under it will return nothing.
Are you just after the dns and username, or do you specifically want to know the values of these environment variables
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 10, 2009 at 3:06 am
actually my requirement is as below...
I have a list of SQL servers, I need to connect to each one of them and get the following details.
1. The OS they are running on
2. The number of Processors
3. The Domain Name
4. The Machine Name(Optional)
A quick reply would help me a lot.
September 10, 2009 at 3:13 am
naresh1253 (9/10/2009)
actually my requirement is as below...I have a list of SQL servers, I need to connect to each one of them and get the following details.
1. The OS they are running on
2. The number of Processors
3. The Domain Name
4. The Machine Name(Optional)
A quick reply would help me a lot.
To be perfectly honest, a call to your wintel team would tell you this in 5 minutes tops. depends on the number of servers, you could do this manually by logging on to each individually. I have written similar scripts in the past but they can differ depending on the sql versions.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 10, 2009 at 3:19 am
Silverfox (9/10/2009)
naresh1253 (9/10/2009)
actually my requirement is as below...I have a list of SQL servers, I need to connect to each one of them and get the following details.
1. The OS they are running on
2. The number of Processors
3. The Domain Name
4. The Machine Name(Optional)
A quick reply would help me a lot.
To be perfectly honest, a call to your wintel team would tell you this in 5 minutes tops. depends on the number of servers, you could do this manually by logging on to each individually. I have written similar scripts in the past but they can differ depending on the sql versions.
sigh
select @@version will give a lot of this.
select serverproperty('ComputerNamePhysicalNetBIOS') for example
if you look at serverproperty, it should help you further
or you can use
exec xp_msver
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 10, 2009 at 4:05 am
thanks for the quick reply.
September 10, 2009 at 6:22 am
I've used this query before to pull service account & domain from the registry. Could work for you.
DECLARE @serviceaccount varchar(100)
DECLARE @domain varchar(1000)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\TCPIP\Parameters',
N'Domain',
@domain OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'
SELECT @Serviceaccount, @domain, @@SERVERNAME
September 14, 2009 at 4:22 am
If you aer a sysadmin and you run xp_cmdshell, it will run using the credentials of the SQL Server service account.
If SQL is run using LOCAL SYSTEM authority, then you will not get domain information because LOCAL SYSTEM is by definition not part of a domain.
The previous posts in this thread show some good alternatives to using SET to get the information you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 14, 2009 at 9:21 am
thanks for that info..that was very useful to me.
September 15, 2009 at 8:59 am
Are you running SSMS on the server itself or on another machine?
If your not running it, and the command prompt, on the same machine then you are obviously going to get different results.
I've been caught out in a similar way several times.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply