SQL Server 2005 Logins

Question

SQL Server 2005 Logins

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Paul G-468777 Old Hand Points: 398 More actions · Answer 1

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 Mr or Mrs. 500 Points: 561 More actions · Answer 2

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 SSC Guru Points: 114642 More actions · Answer 3

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 SSC-Insane Points: 24681 More actions · Answer 4

Excellent article. Thanks Brian for such a good one. 🙂

Tom Garth SSCertifiable Points: 6193 More actions · Answer 5

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[/url]

"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

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 6

Nice article thanks.

One thing I found interesting in 05 is that when you use the UI to script an existing login, it still uses the old syntax!

/****** Object: Login [aamin] Script Date: 04/11/2008 09:58:34 ******/

EXEC master.dbo.sp_addlogin @loginame = N'mylogin', @passwd = @randomPwd, @defdb = N'master', @deflanguage = N'us_english'

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Tom Garth (4/11/2008)
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,

I haven't seen it, but it wouldn't surprise me if the info is one of the management views.

K. Brian Kelley
@kbriankelley

Tom Garth SSCertifiable Points: 6193 More actions · Answer 8

K. Brian Kelley (4/11/2008)
Tom Garth (4/11/2008)
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,
I haven't seen it, but it wouldn't surprise me if the info is one of the management views.

Thanks Brian,

I'll be finding out soon enough. I'll try to remember to post back with the info when I do.

Regards,

Tom Garth
Vertical Solutions[/url]

"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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

TDuffy (4/11/2008)
Nice article thanks.
One thing I found interesting in 05 is that when you use the UI to script an existing login, it still uses the old syntax!
/****** Object: Login [aamin] Script Date: 04/11/2008 09:58:34 ******/
EXEC master.dbo.sp_addlogin @loginame = N'mylogin', @passwd = @randomPwd, @defdb = N'master', @deflanguage = N'us_english'

Check to see what version of SQL Server SSMS is set to script for (Tools | Options | Scripting | and under General scripting options check Script for server version). If it's set for SQL Server 2000, then the script makes sense. Otherwise, it should be scripting for SQL Server 2005, which means using CREATE LOGIN syntax.

K. Brian Kelley
@kbriankelley

TDuffy SSCarpal Tunnel Points: 4170 More actions · Answer 10

You are correct. My setting was for SQL Server 2000. Duh!

Tom Garth SSCertifiable Points: 6193 More actions · Answer 11

Tom Garth (4/11/2008)
K. Brian Kelley (4/11/2008)
Tom Garth (4/11/2008)
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,
I haven't seen it, but it wouldn't surprise me if the info is one of the management views.
Thanks Brian,
I'll be finding out soon enough. I'll try to remember to post back with the info when I do.
Regards,

Update:

Today's featured article:

Identify SQL Server 2005 Standard Login Settings

In a nutshell:

SELECT LOGINPROPERTY('sa', 'IsMustChange');

Tom Garth
Vertical Solutions[/url]

"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

andy.tynan Valued Member Points: 59 More actions · Answer 12

Brian, thanks for a very instructive article.

One question, though:

Is there any way of changing the default for CHECK_POLICY in CREATE LOGIN to be NO, rather than YES?

I have a third-party application that creates new logins as part of creating its own user setup, and it encrypts the database passwords to prevent direct DB access. However, this version is still using sp_addlogin and fails as the encrypted password don't always pass the policy check (which is also out of my control!) so I have to create all the logins manually, which is getting dull.

There is often a trace flag for this kind of thing, but I can't find anything on the 'net.

colin-514459 Old Hand Points: 306 More actions · Answer 13

I stumbled on this article and thread as I searched for help with allowing "remote" users to connect to a SQL Server 2005 Express database. Front-end is an Access "project" (ADP) and computers are merely using the built-in Windows XP peer-to-peer networking. Can anyone advise me or point me to an article that describes how such "remote" users are treated. I seem to be "connecting" with the database but not being "authenticated". Many thanks,

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 14

It depends on the connection settings. How is the Access front end set to connect with SQL Server? Via SQL Server or Windows authentication.

K. Brian Kelley
@kbriankelley

colin-514459 Old Hand Points: 306 More actions · Answer 15

Thanks for the prompt reply.

When in the Access Project on my "development" laptop that also hosts the SQL Server database, Access Options - Server - Connection brings up the "Data Link Properties" dialogue window, whose Connection tab has "Use Windows NT integrated security" selected rather than "Use a specific name and password" as the "information to logon to the server".

As far as I can see, the SQL Server settings are for "Windows authentication". On my "remote" PC I am launching a copy of the Access Project using a "run-time" version of Access. I log on to the remote PC with an identical Windows XP user name and password as I use on my laptop, and both are administrator users.

On my "remote" PC, after my Access Project's initial "splash" screen is displayed, I get the error message "Error Numb : 18456, Error Desc : Joe Cool V2 could not log on to the server. Verify that the log on information is correct". It is at this stage that the Access program would be trying to read the "Operaters" table from the database to allow the user to log on to the application.

"Joe Cool V2" is the name of the shortcut on the remote PC's desktop, rather than a user name.

The shortcut on the remote PC is launching the Access file "JCSQL.accdr", which was produced by an "Installer Package" created on the "development" laptop using Access's "Developer Toolkit". Is ".accdr" the correct suffix for a deployed Access "Project" that starts life as a ".adp" file on the development computer?

Thanks in advance for any further help you can offer. Colin.