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


SQL Server 2005 Logins


SQL Server 2005 Logins

Author
Message
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6820 Visits: 1917
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
@‌kbriankelley
csales
csales
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

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



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6820 Visits: 1917
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
@‌kbriankelley
Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
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
R M Buda
R M Buda
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 300
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
Paul G-468777
Paul G-468777
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
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.
Vernon Jimmerson-306463
Vernon Jimmerson-306463
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 391
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?
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6820 Visits: 1917
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
@‌kbriankelley
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6277 Visits: 1407
Excellent article. Thanks Brian for such a good one. Smile



Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
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

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