NT Authentication Using MSDE

[Ladyhawk]

What level of security does a user have to have to access a local MSDE database through NT Authentication? Ladyhawk.
** ASP/VB/Java Programmer **

 

[tlbroadbent]

The user must be granted access to MSDE. See sp_grantlogin in SQL BOL for details. The login must then be granted access to the datbases and given permissions in those databases. See sp_grantdbaccess and the GRANT command in SQL BOL. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Ladyhawk]

I haven't worried about any of that and some of my users can access their databases OK and some can't. The ones that can't can if I just give them Admin access. So does the NT permissions that someone has make a difference? Ladyhawk.
** ASP/VB/Java Programmer **

 

[tlbroadbent]

If they can login but can't access databases then the SQL permissions are not properly set for them. Have you verified that they have access and required permisisons on the databases or is that something you haven't worried about?

Do you add them to the SQL system admin role to give them admin access? Or are you referring to Windows administration? If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Ladyhawk]

"If they can login but can't access databases then the SQL permissions are not properly set for them. "

Do you mean if they can login into Windows NT? If so, then yes they can. I haven't added any groups or anything to the databases or the master database because I am just using MSDE (which I install during the installation of my application) and the users all belong to different groups on different networks.

"Do you add them to the SQL system admin role to give them admin access? Or are you referring to Windows administration? "

I don't add them to the SQL system admin role because I don't know who they (the users) are going to be. I don't understand why it works for most users on various NT networks, and doesn't for just a handful (2 actually) of NT networks.

Maybe because of this I can't use Windows NT Authentication. The trouble with that is I create a data source for a database programmatically (using VB) and I haven't found a way to not use NT Authentication and use a username and password without the user seeing a login screen. Ladyhawk.
** ASP/VB/Java Programmer **

 

[Ladyhawk]

Does anyone have any ideas about this problem? Ladyhawk.
** ASP/VB/Java Programmer **

 

[tlbroadbent]

Is each user installing MSDE on their own PC or are they all trying to access datasbases in MSDE running on another computer?

When you say the two users can't access databases until you give them Admin rights, do you mean that you make them Windows Administrators and then they can login to SQL Server? By default, SQL Server allows loal windows administrators access to SQL Server as SQL System Administrators. Most people remove the built-in administrators from SQL Server and grant Access via another NT or Windows security group. If you don't want the all users to be administrators then you must grant the users access to MSDE via Group or individual logins.

If you don't want worry about this stuff, just make all the users Windows Administrators and let them use MSDE. However, if you want some control over the databases, then setup security properly. It doesn't happen automatically. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Ladyhawk]

Each user is installing MSDE on their machine.

When I say I give them admin rights, I mean I make them Network Administrators. I want the user of the machine with MSDE on it to be able to access it. So you are saying for a user to use MSDE on their machine, they must be a local administrator or part of a group that SQL Server knows about.

Since I can't make them local administrators (IT support won't let me because they think the users will break their own machines)... I therefore, it seems, need to create a SQL Server user group that the users can belong to. Since this is MSDE, I need to be able to create a SQL Server user group and every time someone different tries to use my software.. I need to add them to the group to give them access.... all through code.

Does this sound right?

BTW: I didn't worry about all this stuff because I thought MSDE was a desktop database. My mistake. Ladyhawk.
** ASP/VB/Java Programmer **

 

[tlbroadbent]

I've only installed MSDE 1 (SQL 7) and that is on my home PC with Windows 98 so Windows Authentication is not an option. I have confirmed that the MSDE 2000 install on NT 4.0 or Windows 2000 will place the local administrators group in the sysadmin role for the instance of MSDE. I don't know if there is an option available during MSDE setup that allows another Windows login to be added to the sysadmin role.

Fortunately, Microsoft has provided a quite a bit of information on its website regarding MSDE. You may already be aware of much of this info. The following link is a good place to start. It contains links to articles about installation, distribution, connecting, backups, security, etc.

Choosing and Using MSDE 2000 as the Database Engine for Your Application

http://msdn.microsoft.com/library/en-us/dnmsde/html/msderoadmap.asp

Implement Security and Authentication with MSDE 2000

http://msdn.microsoft.com/library/en-us/dnmsde/html/msderoadmap.asp?frame=true#msderoadmap_topic7

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm