logins and groups

Question

Hi Team,

How can i get all the groups and all the logins in the instance.

can you guy please provide me, a script for this.

Thanks in advance..

tx

---

---

subu

Answer 1

Hi Team,

How can i get all the groups and all the logins in the instance.

can you guy please provide me, a script for this.

Thanks in advance..

tx

---

subu

Answer 2

sys.server_principals

sys.server_role_members

Answer 3

select name,type_desc from sys.server_principals

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Hi You can use this query as per your requirment

SELECT
    P.NAME AS DATABASE_ROLE,
    DP.NAME AS DATABASE_USER
FROM
    SYS.DATABASE_ROLE_MEMBERS RM
JOIN 
    SYS.DATABASE_PRINCIPALS P ON (RM.ROLE_PRINCIPAL_ID = P.PRINCIPAL_ID)
JOIN 
    SYS.DATABASE_PRINCIPALS DP ON (RM.MEMBER_PRINCIPAL_ID = DP.PRINCIPAL_ID) 

---

------------------ Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Maqbool.

Answer 5

Hi Team,

I have tried the bove scripts but no luck.. can you please see below output

RSExecRole   DEVWEB\sql2k8svc
db_owner    dbo
db_owner    DEVWEB\NAEAST Webops Oncall
db_owner    ReportUser
db_datareader DEVWEB\CIGIST

but i am expecting all groups and all logins with out duplicate

can you please help me on this.

---

subu

Answer 6

Hi URi,

as per your suggestion i have follow but no luck  i m getting all login and users and groups every thing i will get .

i m expecting all Groups in a instance with out duplictae

all logins with in a instance with out duplicate.

---

subu

Answer 7

Hi,

What is wrong with Alexey's suggestion of sys.server_principals? I believe this answers your question. The following query shows you all the logins in the instance (assuming you have the appropriate permissions to see them) and the type of login (which will be one of SQL_LOGIN, WINDOWS_LOGIN, WINDOWS_GROUP, SERVER_ROLE, CERTIFICATE_MAPPED_LOGIN, ASYMMETRIC_KEY_MAPPED_LOGIN).

SELECT name, type_desc FROM sys.server_principals

Ben



Answer 8

you can use this one

select name,loginname, isntgroup,* from sys.syslogins

Answer 9

why do you've 2 thread started?

this thread is the same as this one logins and groups and you got the answers there in a single place.

Could a Moderator merge those two threads?

Answer 10

If you are looking for script to retrieve permission then you can try the scripts below

http://sql-articles.com/scripts/script-to-retrieve-security-information-sql-server-2012-denali-and-above/

http://sql-articles.com/scripts/script-to-retrieve-security-information-sql-server-2005-and-above/

---

Mark as ANSWER if I helped you today :-)

Answer 11

Hi Team,

I m sorry for posinng this same question in other plances because

last Friday i have posted i should not get any suggestions from the team that is the  reason i have posted same questions  in other also

appologies from my end  i have trouble to the team

Thanks guys..

---

subu

Answer 12

Hi Team,

I m sorry for posinng this same question in other plances because

last Friday i have posted i should not get any suggestions from the team that is the  reason i have posted same questions  in other also

appologies from my end  i have trouble to the team

Thanks guys..

---

subu

don't mind about posting it in 2 forums. The problem with 2 thread is that you get different oder duplicate answer to your question.

Apart from that, have your question been answered or what is still unanswered?