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
TDuffy
TDuffy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

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

Group: Moderators
Points: 6828 Visits: 1917
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
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: 1015 Visits: 1499
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

"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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6828 Visits: 1917
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
TDuffy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1326 Visits: 57
You are correct. My setting was for SQL Server 2000. Duh!



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: 1015 Visits: 1499
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
[url=http://exchange.verticalsol.com/exchweb/bin/redir.asp?URL=http://www.sqlservercentral.com/links/523963/39436][/url]

In a nutshell:
SELECT LOGINPROPERTY('sa', 'IsMustChange');

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

andy.tynan
andy.tynan
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: 2
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
colin-514459
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

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

Group: Moderators
Points: 6828 Visits: 1917
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
colin-514459
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 36
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.
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