NT Authentication 1

[sifitz]

I have setup up my SQL 7.0 DB to use NT authentication and I have created and NT user. The NT user should have limted access to some databases, however when the NT user logs in (opens SQL server when logged into Windows 2000 pro), the user can see everything and has no restrictions.

I have installed SP3 and this helped but didn't fix the problem.

Can anybody give me some advice, please? I am sure that it should be working.

Thanks

Si Fitz

 

[Bernadette]

Hi There


What roles does your user belong to ? What permissions have you assigned them ?

Bernadette

 

[BeckahC]

If your user is an admin on the server, that might cause this... BeckahC

 

[Bernadette]

Yes, as BeckahC said, check that your user is not a member of the SysAdmin or db_owner roles. Bernadette

 

[sifitz]

The NT user has only got 'public' database access to a database called ITAssist. He has got no other database access roles or server roles.

The public role doesn't have any of my user-defined tables and stored procedures ticked in the Role properties window but does have some crossed out. My user can still see these crossed out objects as well as the other user-defined objects.

He can get into another database called BNL_Core (which I want to restrict access to) and can also add/edit users and other adminstration functions. The "SELECT CURRENT_USER" command returns 'dbo' when using NT authentication. With SQL Server authentication for a Standard User(SQL Server user), the correct current user is returned.

 

[Bernadette]

If I understand it, you have one server with two databases on it. "ITAssist" and "BNLCore", besides others.

I dont know what is causing it, but you could try deleting the user from the databases and adding him in again, just in case there is an internal corruption in the database.

Try running the following to delete the user from the database.


USE master
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
Delete from syslogins where name = 'DOMAIN\USER'

Use ITAssist
Delete from sysusers where name = 'DOMAIN\USER'

Use BNLCore
Delete from sysusers where name = 'DOMAIN\USER'

Hope This Helps
Bernadette

 

[sifitz]

I have deleted the user, and he can still log into a database he shouldn't, without a password.

I deleted the guest user (used when no matching NT login id exists for person logging in). I thought that if I didn't have a matching login or a guest login then there would be no access to any user not in the login list.

When the user opens Query Analyser, it shows their NT username (even though they don't have an NT login into SQL server) and CURRENT_USER gives 'dbo'. However, dbo in the user list for the database has a different login name.

It appears that there is a problem with the guest account or something similar.

Si Fitz

 

[tlbroadbent]

Is the NT user an NT System Admin. By default, NT System Admins are SQL System Admins. Look for BUILTIN\Administrators under logins on the Server. If you want to prevent NT Admins from being SQL Admins, check the article at the following link.

http://support.microsoft.com/support/kb/articles/q263/7/12.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[sifitz]

Thank you so much. I have spent ages on this and this has been the solution. I read so many documents about this and not one of them mentioned this.

Thanks

Si Fitz