Authentication 1

[dbomrrsm]

Sorry if this is a repeat question but I have searched and cant find anything specifric to do with my confusion.

I am using SQL Server 2000 and when I open up EM and right click on the server its registration login is sa so if I open up QA from within EM then I am logged into QA as sa.

If I open up QA independently I am logged into QA as my NT user say ABC and this causes me concern.

There is no user ABC on any of the databases on this particular server and yet user ABC can see and has access to all the databases on the server.

What I want to do is set up users to only have access to one of the databases whilst using Windows authentication.

Thanks in advance for any advice.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[mutley1]

Is the BUILTIN\Administrator login still on SQL Server (check under Security / logins in EM). If so, is the domain use you are sucessfully loging in as a member of the local admin (or any of the groups that are local admin) on the windows side?

HTH,

M.

 

[dbomrrsm]

Mutley

Firstly thanks for the quick response.

The BUILTIN\Administrator is still showing in EM - how do I tell, if the user I am logging on as (that shouldnt have access to certain databases), is a member of the local admin (or any of the groups that are local admin) on the windows side?.

I dont believe that the user id that gets in with full access is likely to have admin priviledges.

How would you normally set up a user to only have access to certain databases ?

Thanks again.


[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[mutley1]

Hi DBomrrsm,

If the user is not in the logins section on EM outright then check the server groups. Right click on "My computer" on the server, select "manage". Go to Local groups and have a look under administrator group (or Local admin - no in front of a server so cant remeber the specifice off the top of my head!). It the domain account is not in the administrators group, make a note of any windows groups that are and check in user manager / AD if the user you are using is in any of those groups.

To give a user access to only certain databases, then go to the logins part of EM, right click and "add new user". Enter the credentiald (domain, username etc..) and then on the last tab (database access) tick the DB you want them to have access to. In the lower section of that window you will see database permissions. Assign those permissions you want them to have for that database (check out BOL for details - you can also create custom roles). It can get nitty gritty as to what you want people to see in a DB so I can't give exact details for your system!

HTH,

M.

 

[dbomrrsm]

Mutley

Thanks for the advice.

Have a star and a nice day.

Thanks again.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[mutley1]

Glad to help. Good luck!