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 Friday, April 11, 2008 9:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, 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'



Post #483752
Posted Friday, April 11, 2008 9:24 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:41 AM
Points: 6,621, Visits: 1,852
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, 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 #483789
Posted Friday, April 11, 2008 9:29 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 04, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #483794
Posted Friday, April 11, 2008 10:34 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:41 AM
Points: 6,621, Visits: 1,852
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, 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 #484030
Posted Saturday, April 12, 2008 12:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, Visits: 57
You are correct. My setting was for SQL Server 2000. Duh!


Post #484040
Posted Monday, April 21, 2008 9:28 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 04, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #488051
Posted Thursday, July 24, 2008 6:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 24, 2009 10:00 AM
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.
Post #540053
Posted Friday, January 09, 2009 6:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:44 AM
Points: 8, Visits: 33
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,
Post #633344
Posted Friday, January 09, 2009 2:48 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 7:41 AM
Points: 6,621, Visits: 1,852
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, 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 #633914
Posted Saturday, January 10, 2009 6:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 5:44 AM
Points: 8, Visits: 33
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.
Post #634144
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse