Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005 script Expand / Collapse
Author
Message
Posted Thursday, February 4, 2010 6:43 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 4:09 AM
Points: 761, Visits: 1,083
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
Posted Thursday, February 4, 2010 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 10,381, Visits: 13,440
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
Posted Thursday, February 4, 2010 7:24 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 4:09 AM
Points: 761, Visits: 1,083
a list of Windows Logins and Groups that have access to the SQL Server
Post #859650
Posted Thursday, February 4, 2010 7:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 10,381, Visits: 13,440
Okay, here's a simple one:

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





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 #859668
Posted Thursday, February 4, 2010 7:53 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 4:09 AM
Points: 761, Visits: 1,083
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
Posted Thursday, February 4, 2010 8:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 10,381, Visits: 13,440
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

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 #859686
Posted Tuesday, July 6, 2010 6:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, June 30, 2014 4:21 PM
Points: 39, Visits: 185
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
Posted Thursday, May 24, 2012 3:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse