SQL Server 2005 Logins

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Brian Kelley » SQL Server 2005 Logins

36 posts, Page 2 of 4

««1 2 3 4 »»»

SQL Server 2005 Logins

Rate Topic

Display Mode

Topic Options

Author

Message

K. Brian Kelley

Posted Thursday, October 12, 2006 9:27 PM

Keeper of the Duck

Group: Moderators
Last Login: Today @ 4:47 PM
Points: 6,584, Visits: 1,788

Actions taken in SQL Server have no effect on the domain. If you disable a login or group in SQL Server, it only affects SQL Server. However, changes in the domain, since that is the starting point, does affect SQL Server. So if a particular account is disabled, then it will not be able to access SQL Server, either.

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #315152

csales

Posted Sunday, November 12, 2006 11:59 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 08, 2010 1:36 AM
Points: 31, Visits: 20

I have a large number of logins that have been disabled. I would like to drop these logins and would prefer to script this. I've looked through the syslogins table and can't find an indicator which refers to whether an account is enabled/disabled. Anyone have any ideas as to where this is?

thanks.

Post #322212

K. Brian Kelley

Posted Monday, November 13, 2006 7:17 AM

Keeper of the Duck

Group: Moderators
Last Login: Today @ 4:47 PM
Points: 6,584, Visits: 1,788

If you are using SQL Server 2005, don't use syslogins. Instead use the system management view sys.sql_logins. There is a column, is_disabled, which is a flag for whether or not a login is disabled or not. To make it easy on us, there is the name column as well in that one view. That should allow you to script what you want fairly easily.

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #322276

Tatsu

Posted Friday, December 22, 2006 7:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:43 AM
Points: 287, Visits: 213

I am looking forward to the article on Certificate logins. I think that will go a long way towards eliminating SQL Logins for cross-platform applications.

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog

Post #332502

Renato Buda-153382

Posted Friday, December 29, 2006 4:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 25, 2013 8:57 PM
Points: 139, Visits: 214

Hi Brian,
Great article. When I read it, it reminded me of something I have never understood about windows logins.

Say I have active directory with user "U" who belongs to 2 security groups, "GrpA" and "GrpB". I create 3 windows logins on SQL Server for "U", "GrpA" and "GrpB". Now when "U" logs on to the server which login is being used? And if I drop the login for "U", the user "U" can still access the server via a group, but which one?

Thanks,
Renato

Post #333404

Paul G-468777

Posted Tuesday, July 24, 2007 12:23 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, October 29, 2012 11:24 AM
Points: 22, Visits: 84

I enjoyed this article very much but it further exposed my ignorance.

I am not having much success working my way through security using the interface in Management Studio. I am a longtime developer that is inheritting a DBA role for a while and I am DESPERATELY searching for a n article/book/series that will help me understanding correctly scoping privileges, permissions and just what the heck some of these choices are.

I want to make sure I give the users "just the right size" permissions and fear opening things up too wide just so the few developers we have can get access.

If I can understand the "why" and "What" I think I can succeed. Any help/suggestions/guidance is appreciated.

Post #384745

Vernon Jimmerson-306463

Posted Friday, November 16, 2007 11:18 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, February 17, 2013 10:50 PM
Points: 12, Visits: 335

Unfortunately the sys.sql_logins table does not include domain logins.
Do you know how to find similar information to the sys.sql_logins information for domain accounts?

Post #423175

K. Brian Kelley

Posted Friday, November 16, 2007 12:16 PM

Keeper of the Duck

Group: Moderators
Last Login: Today @ 4:47 PM
Points: 6,584, Visits: 1,788

The DMV sys.server_principals contains basic information on all logins. That should give you the information you need from a SQL Server perspective with respect to domain accounts. If you need password policy settings, etc., you're going to have to get that from the local security policy or default domain policy (group policy).

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog |

LinkedIn | Twitter

Post #423186

Anipaul

Posted Thursday, April 10, 2008 10:24 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 4,787, Visits: 1,335

Excellent article. Thanks Brian for such a good one. :)

Post #483404

Tom Garth

Posted Friday, April 11, 2008 7:36 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499

Great article Brian!

An application that I wrote has an interface for the app admin to create and manage users, and role memberships. It has been running on SQL 2000, but I will be migrating it this year to SQL 2005 (or maybe 2008).

Does SQL Server 2005 store any of the policy password rules locally. I'm wondering if I will be able to determine when a user's password is due to expire just by checking the availalbe SQL Server information.

Thanks,

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Post #483662

« Prev Topic | Next Topic »

36 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions