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 ««1234»»»

SQL Server 2005 Logins Expand / Collapse
Author
Message
Posted Thursday, October 12, 2006 9:27 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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
Posted Sunday, November 12, 2006 11:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 8, 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
Posted Monday, November 13, 2006 7:17 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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
Posted Friday, December 22, 2006 7:38 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 1:56 PM
Points: 295, Visits: 281
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
Posted Friday, December 29, 2006 4:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 12, 2014 3:39 AM
Points: 146, Visits: 244
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
Posted Tuesday, July 24, 2007 12:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, November 16, 2007 11:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:29 PM
Points: 12, Visits: 373
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
Posted Friday, November 16, 2007 12:16 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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
Posted Thursday, April 10, 2008 10:24 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:46 AM
Points: 5,404, Visits: 1,400
Excellent article. Thanks Brian for such a good one. :)


Post #483404
Posted Friday, April 11, 2008 7:36 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 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 »

Add to briefcase ««1234»»»

Permissions Expand / Collapse