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 2005 Forums
 SQL Server Administration (2005)
 Show logins

Author  Topic 

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-30 : 17:22:33
I am creating sp on database SYSADM (This is my own database i created).

In sp i like to run following:

If i use FINANCEDB i get all the logins on this database
USE FINANCEDB

SELECT s.name as [Login Name], d.name as [User Name],
default_schema_name as [Default Schema]
FROM sys.server_principals s
JOIN sys.database_principals d
ON d.sid = s.sid;

How do i run this by passing in the DB_ID of FINANCEDB so i only
get the logins that have access to FINANCEDB.

I just need it to return yes or no.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-08-31 : 05:34:26
If you run that from within FINANCEDB, you will only get the logins with access to that DB only. Database_principals is a dataabse-specific view that lists the users in the DB.

Login names are server-wide, user names are per database.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-08-31 : 17:30:35
I put the databasename in front of sys.server_principals that worked
Cheers
Go to Top of Page
   

- Advertisement -