Check username and password - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Check username and password

Author

Topic

daquoc
Starting Member

35 Posts

Posted - 2005-07-06 : 03:02:50

Hi all experts

Could you pls tell me what sp_ which is used to check one user ('aa' for example) if can access the database in SQL 2000.

I'm looking for system procedure sp_check_user_accessdata. May be ?

Thank. Nice working day.
daquoc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-06 : 03:24:19

Is this?

select name,dbname from master..syslogins

Madhivanan

Failing to plan is Planning to fail

daquoc
Starting Member

35 Posts

Posted - 2005-07-06 : 21:35:51

Thank. But when I tried your advice, I only got one username default for one database depend on the Default value in SQL Server login Properties (tab security). Here is my result.

Name dbname
ADMIN master
sa master
Q08 master
......

Suppose that my database named ACCOUNT and username ADMIN access to ACCOUNT (of course I set the default value in SQL Server login Properties (tab security) to ACCOUNT.

After "select name,dbname from master..syslogins"

Name dbname
ADMIN ACCOUNT
sa master
Q08 master

.............

And ADMIN also access to another database : HRMS_DATA for example.

Name dbname
ADMIN ACCOUNT
sa master
Q08 master

.............
the result is still that while ADMIN can access HRMS_DATA

if "select name,dbname from master..syslogins"
Name dbname
ADMIN ACCOUNT
ADMIN HRMS_DATA
sa master
Q08 master
then is OK

Could pls offer me another solution
Thank. Nice working day
daquoc

.............

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-07-07 : 23:59:09

You need to check the sysusers table of the particular database. If there is a SID in that table matching the SID in the master..syslogins table, then the login has a user in the database and can be granted permissions. You can then check to see if the user has permissions by looking at the syspermissions table. The grantee in that table is an ID in the sysusers table. The id in that table is an object in the sysobjects table.

blah, blah, blah

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

schuhtl
Posting Yak Master

102 Posts

Posted - 2005-07-08 : 10:11:22

Does this help?

sp_helplogins 'login goes here'

daquoc
Starting Member

35 Posts

Posted - 2005-07-12 : 22:14:58

Thank you so much
I'll try my best.
Nice working day.
daquoc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources