SQL Server 2005 script

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL Server 2005 script

SQL Server 2005 script

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Thursday, February 04, 2010 6:43 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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?

Post #859608

Jack Corbett

Posted Thursday, February 04, 2010 7:21 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #859646

river1

Posted Thursday, February 04, 2010 7:24 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Post #859650

Jack Corbett

Posted Thursday, February 04, 2010 7:45 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Okay, here's a simple one:

SELECT
    *
FROM
    sys.server_principals AS SP
WHERE
    type IN ('U','G')

Post #859668

river1

Posted Thursday, February 04, 2010 7:53 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Post #859673

Jack Corbett

Posted Thursday, February 04, 2010 8:07 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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.

Post #859686

SQL Marathoner

Posted Tuesday, July 06, 2010 6:48 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 5:39 PM
Points: 39, Visits: 174

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".

Post #948253

mdhana_bsc

Posted Thursday, May 24, 2012 3:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 24, 2012 3:45 AM
Points: 1, Visits: 0

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.

Post #1305653

« Prev Topic | Next Topic »

Permissions