Login ID and LOGIN name

Question

Hi team,

 

i have one instance i need List of LOGIN NAMEs and LOGIN ID

below script i get only on login name only i want loginID also please the correct script.

 

Thanks in advance.

 

SELECT name FROM master.dbo.syslogins

---

subu

Answer 1

SELECT sid, Name FROM sys.syslogins

Try this...

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 2

Hi Pete,

 

thank you for your information

 

it is working very fine

 

But right know  the issue is

i have table called LOGINFO i have to insert the data LOGID  LOGINNAME also manually i have to insert or

is there any short method is there plese help me on this

 

Thanks in advance..

---

subu

Answer 3

Try something like this...

INSERT INTO LogInfo(LogID, LoginName)
SELECT sid, Name
FROM sys.sysLogins

 

Hope this helps.

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 4

What do you mean by "manually" and "short method" ???

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 5

Hi Team,

 

My work is increased bit know

 

 i have around 8 instances i have to insert all logins with out duplicate in 8 instance i have one table  i have to insert all users with out duplicate in that table onlye  can you any body please suggest me how can we do .. this.

 

its bit difficult to me to find out please help me.

---

subu

Answer 6

INSERT INTO Srv1.dbo.LogInfo(LogID, LoginName)

SELECT sid, Name

FROM Srv1.sys.sysLogins

UNION 

SELECT sid, Name

FROM Srv2.sys.sysLogins

UNION 

SELECT sid, Name

FROM Srv3.sys.sysLogins

UNION 

SELECT sid, Name

FROM Srv4.sys.sysLogins

UNION 

SELECT sid, Name

FROM Srv5.sys.sysLogins

UNION 

SELECT sid, Name

FROM Srv6.sys.sysLogins

UNION 

SELECT sid, Name

FROM Srv7.sys.sysLogins

UNION 

SELECT sid, Name

FROM Srv8.sys.sysLogins

You will need first to set-up linked servers. You can do this through the GUI. See this link http://msdn.microsoft.com/en-us/library/aa560998(v=bts.10).aspx

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/

Answer 7

Hi Pete

 

I want with out duplicate the above code is work right..

 

because before only i have to check it right.

 

please help me.

 

Tx

---

subu

Answer 8

Hi, try this...

INSERT INTO Srv1.dbo.LogInfo(LogID, LoginName)



SELECT DISTINCT sid, Name FROM

(



SELECT sid, Name



FROM Srv1.sys.sysLogins



UNION SELECT sid, Name



FROM Srv2.sys.sysLogins



UNION SELECT sid, Name



FROM Srv3.sys.sysLogins



UNION SELECT sid, Name



FROM Srv4.sys.sysLogins



UNION SELECT sid, Name



FROM Srv5.sys.sysLogins



UNION SELECT sid, Name



FROM Srv6.sys.sysLogins



UNION SELECT sid, Name



FROM Srv7.sys.sysLogins



UNION SELECT sid, Name



FROM Srv8.sys.sysLogins

)

 Although, be aware, that any SQL Logins (As opposed to Windows based logins), will have a different SID on each server, even if they have the same name, so these would be treated as distinct.

---

Peter Carter http://sqlserverdownanddirty.blogspot.com/