Users and databases

[OldSkoolSkater]

Hello

Is there a way in Enterprise Manager to find out how many users (and who they are)are connected to a database?
I can only think to use server manager and find who is connected to the server, but there must be a better way!
We run SQL7 SP1

Cheers

Steve

 

[RickCole]

In Enterprise Manager navigate to Management then Current Activity and finally Process Info.

Another way is by running the stored procedures sp_who or sp_who2 from a Query Analyser window.

Also you could run the query:

select * from master..sysprocesses


Rick.

 

[tlbroadbent]

You can also run the system stored procedures - sp_who or sp_who2. Run them from Query Analyzer, OSQL or another tool that will allow you to execute SPs. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[OldSkoolSkater]

Thanks for that guys

I later realised that all users access the database using the same username, so when I ran your checks all I got was 40 users with the same name!
It was apparently done this way so users accessing SQL for crystal reports purposes could have a unified logon (it's too taxing for them to have another logon to remember)
Is this good working practise, or should I really get it set up so that all users have their own user id's in SQL?

Cheers

Steve

 

[RickCole]

Only my personal opinion here but I hate it where users all come into SQL with the same username.

I do have one server, which uses some third party software and works in this manner. I have had problems before where I needed to know who was running one particular process and couldn't find out!! Well not without a bit of hassle anyway!!

Also I feel the security side of things is a little out of my control and that always makes me feel a little uncomfortable.

In some cases you may not be able to avoid it but with the ease of use and added flexibility in the security model on SQL 7.0 upwards I would change it if I could.

Rick.

 

[tlbroadbent]

sp_who and the Current Activity list in EM show the hosname in addition to login. However, if you use an n-tier application, the hostname will be the same also.

Most of our applications use a common SQL login. Some require each user to have a SQL login. There are advantages with both methods. Obviously, for security and traceability having each user login with a personal login is preferred. From an administrative point of view, one login for everyone using an application is preferred. In this case, the SQL login is hidden to the users and we add an application login for security. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[OldSkoolSkater]

>>In this case, the SQL login is hidden to the users and we add an application login for security.

Same for us Terry, I'll maybe leave it setup this way until I find out more about SQL

Cheers

Steve