SQL to get users for a Database

[sunila7]

Hi All,

I want to write a SQL to display users for a particular database, who have logins to a SQL server database

Thanks

Sunil

 

[essa2000]

Dear ;

You can use system table to get this information like ;

Select * from pubs.dbo.sysusers Where IsLogin = 1

I am confused with your question either you want to see the currently logins user on a database or just want to see which I mentioned in the above query.

Regards,
Essa

 

[sunila7]

I had tried that query it gives me all the users.... some of them which does not show up on the user list for a database in the enterprise manager.

i will explain more clearly, what i am trying to achieve....

i want the list of users being displayed in enterprise managers user list for a database...... I hope i am clear enough.....

thanks

Sunil

 

[darweish]

Dear Sunil

I think You can use following query,

Select * from pubs.dbo.sysusers Where IsLogin = 1
and hasdbaccess = 1

Regards,
essa2000

 

[sunila7]

Dear Essa,

I tried that also but it still returns rows which are not user anymore..... i am not sure how that happens.... the sysusers table has some rows in it that r not being shown in enterprise manager.

Could there something more to it.......

this is the sql i was using...

Select * from sysusers Where islogin = 1
and hasdbaccess = 1 and issqlrole = 0

Thanks

Sunil

 

[sunila7]

Hi,

I have been doing some research on this..... i think this is the SQL that should be used...

Select su.name from sysusers su,master..syslogins sl Where IsLogin = 1
and hasdbaccess = 1 and su.sid = sl.sid order by su.name


Sunil