| Author |
Topic |
|
ConnieXujuan
Starting Member
48 Posts |
 Posted - 2011-12-21 : 19:10:08
|
Hi -,I noticed that when I was using xp_cmdshell to retrieve data for servers and databases users from table [sys.database_principal] it behave differently (gave me different answers) running on local SQL server instance and other SQL server instances....Could anyone please let me know why is that and how do I fix the problem please?Thanks a lot!Connie  |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-22 : 17:15:51
|
| Hi Brett,Sorry I don't understand what you meant by those instances are identical.... do you mean they are of the same version of SQL servers?Thanks!Connie |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-22 : 17:47:59
|
| Hi Tara,they are different serves but I am accessing the same table: the master.sys.database_principals on serverA.Connie :) |
|
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-22 : 17:49:01
|
| I meant I was just querying the same table from different servers..... Could that lead to different running results? |
|
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-22 : 18:15:23
|
| Hi there,It is querying the same table. I logged in SQL server using windows login, and connected to a local instance (serverA) as well as another server instance (serverB); however, when I was running query: SET NOCOUNT ON SELECT * FROM master.sys.database_principals WHERE type IN ('U') on the local instance it gave me correct answer; but if I run query: exec xp_cmdshell 'sqlcmd -S "ServerA" -Q "SET NOCOUNT ON SELECT * FROM master.sys.database_principals WHERE type IN (''U'')" -s "|" -W'from the other instance (ServerB) I was connected to, it gave me a different answer.Connie |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-22 : 21:31:11
|
| Hi Tara,I am sure it is not just sorting it differently cause the time when i was running it on serverB gave me 5 rows while the time when I was running it on a local instance it gave me only 4 rows.Connie |
|
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-22 : 21:37:13
|
| Also, if I was to query for SQL logins ( where type in 'S') it gave me different results too. the running results running on local instance is: dbo, guest, INFORMATION_SCHEMA and sys; but if I run it on instance serverB it gave me one more result: dwuser.I noticed something: if on instance serverB i run below query: select distinct (d.name) from UsersDatabaseCurrent d, UsersServerCurrent s where d.name = s.name and d.DatabaseName = 'master'it gave me the extra result. I was just wondering why this is happening?Thanks a lot!Connie |
|
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-22 : 21:40:57
|
| i was using SQl logins as an example cause it gives me less results and it was more convenient to type.tables [UserDatabaseCurrent] and [UsersServerCurrent] are contains all the users for databases on all server and all servers; they are populated by extracting info from sys.database_principals and sys.server_principals |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-12-22 : 23:31:21
|
you mean you are executing this querySELECT * FROM master.sys.database_principals WHERE type IN ('U') and different SQL Server and you expect the same result to return ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-22 : 23:54:29
|
| Hi KH,yes I am running the query above from two different servers (serverA and serverB) but querying the same table (serverA.master.sys.database_principals).the query i ran on serverA (local instance) is as below:SET NOCOUNT ON SELECT * FROM master.sys.database_principals WHERE type IN ('U') the query I ran on serverB (the other instance) to query the master database users for serverA is as below:exec xp_cmdshell 'sqlcmd -S "ServerA" -Q "SET NOCOUNT ON SELECT * FROM master.sys.database_principals WHERE type IN (''U'')" -s "|" -W'Since they are querying the same table, the results returning should be the same right?Connie |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-12-23 : 01:32:18
|
quote:
Since they are querying the same table, the results returning should be the same right?
No. same table but different Server. Unless you have created the same users in both of the servers
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-25 : 05:08:18
|
| Hi Tara,yeah that was what I meant. I was querying the same system view master.sys.database for the same server (serverA) both locally and remotely;but i got different results.Could you please have a look at the query I used:locally: SET NOCOUNT ON SELECT * FROM master.sys.database_principals WHERE type IN ('U') remotely: exec xp_cmdshell 'sqlcmd -S "ServerA" -Q "SET NOCOUNT ON SELECT * FROM master.sys.database_principals WHERE type IN (''U'')" -s "|" -W'if I am not actually querying the same server, what was wrong with my query?Thanks a lot!!!! Connie |
|
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-25 : 05:21:00
|
| I have noticed though, when I was querying other tables or views both locally and remotely querying gave me correct answers, but when it came to querying about logins (such as: syslogins etc.) the running results are different locally and remotely...I was so confused.... |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-12-25 : 09:17:32
|
| When you run xp_cmdshell, it runs in the context of the service account. When you run then use SQLCMD without specifying the login credentials it will use Windows Authentication of the service account from ServerB.In this case, that login only has PUBLIC access and therefore can only see the public database principals avaialble in the master database.Specify a different user login for SQLCMD - or grant the service account higher rights on SystemB to see additional database principals.Jeff |
|
|
|
ConnieXujuan
Starting Member
48 Posts |
Posted - 2011-12-27 : 17:23:57
|
| Thanks Jeff! I think that is the problem. I have used an account that has higher permission and had a look.... it is now correct! Connie :) |
|
|
|
Next Page
|
xp_cmdshell behave differently