SQL Server 2005 script

Question

SQL Server 2005 script

river1

SSC-Insane

Points: 24300
More actions
February 4, 2010 at 6:43 am

#137816

Hi,
I want to view by select if there are windows logins in my SQL Server instance, is this possible?
For example, if i have the login windons\martha inside my SQL Server security i want that the select statement return this to me. how can i do this?
Thank you.
P.s - is it possible to make a select in SQL Server instance and verify if the Domain admins are members of the builtin\administrators , at the OS level?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

I'm not sure exactly what you are asking. Do you want a list of Windows Logins and Groups that have access to the SQL Server or do you want to know the user name of the person executing a query?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

river1 SSC-Insane Points: 24300 More actions · Answer 2

a list of Windows Logins and Groups that have access to the SQL Server

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Okay, here's a simple one:

SELECT

*

FROM

sys.server_principals AS SP

WHERE

type IN ('U','G')

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

river1 SSC-Insane Points: 24300 More actions · Answer 4

Thank you. that resolved my problem

Waht about the other problem? can i make a query that returns if the Domain Admins are in the group of the builtin\administrators, at the OS level?

If not, do you know how can i achieve this,e.g, in Visual Basic?

Thank you

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

There are definitely some drawbacks here. You know what groups and individuals have access, but there is no way within SQL Server who are members of a Windows group. I believe that there are some PowerShell scripts/modules out there that get that information. Here's a link to a blog post that includes session information from a SQLSaturday session that included getting permissions using PowerShell.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQL Marathoner SSC-Addicted Points: 417 More actions · Answer 6

To a certain extent, you can query if the Domain Admins are in the group of the builtin\administrators. Try this:

exec xp_logininfo 'BUILTIN\Administrators', 'members';

Note: it returns only the highest privilege level of Windows user or group.

For example, if there is a Windows group "MyDomain\DomainAdmins" that belongs to group "BUILTIN\Administrators", then you will see group "MyDomain\DomainAdmins" in the above xp_logininfo result. But you won't be able to see the windows groups or users who are in the group of "MyDomain\DomainAdmins".

mdhana_bsc Grasshopper Points: 13 More actions · Answer 7

I have a obligation here,

I have a set of servers for which i need to find the sql server credentials. This is just to change the passwords.

can some one help me to provide sql script or VB script with ADO connection.

Exact requirement : I have a input file "sql.txt" with server names, i need the credentials to be displayed for all those server names.