Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 script


SQL Server 2005 script

Author
Message
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
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?
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
a list of Windows Logins and Groups that have access to the SQL Server
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
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
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
SQL Marathoner
SQL Marathoner
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 194
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
mdhana_bsc
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search