Post reply

Query to Select Login & User ID

  • November 18, 2008 at 9:26 am

    #124321

    Please post any SQL Query to select the SQL Logins associated with Database User ID's. Is there any system views or it is available only through SSMS GUI?

    Thanks.

  • November 18, 2008 at 9:37 am

    #900014

    SELECT * FROM sys.database_principals

    WHERE TYPE = 's'

  • November 18, 2008 at 9:47 am

    #900023

    There's also this:

    select b.uid, b.name as username,a.name as login

    from master.dbo.syslogins a, sysusers b

    where a.sid = b.sid

    Thanks,

    Lori

  • November 18, 2008 at 1:52 pm

    #900165

    select db_name(), b.name as username,a.name as login

    from master.dbo.syslogins a, sysusers b

    where a.sid = b.sid and b.issqluser = 1

    The above query was very useful for my scenario. I also added issqluser just to track down by sql login.

    tnk y'all.

  • November 18, 2008 at 10:02 pm

    #900300

    DECLARE @sql varchar(8000)

    DECLARE @BAK_PATH VARCHAR(4000)

    declare c_bak cursor FAST_FORWARD FOR

    select name from sys.databases

    OPEN c_bak

    FETCH NEXT FROM c_bak INTO @BAK_PATH

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @sql = 'sp_helpuser'

    PRINT (@SQL)

    EXEC(@SQL)

    FETCH NEXT FROM c_bak INTO @BAK_PATH

    END

    CLOSE C_BAK

    DEALLOCATE C_BAK

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • November 18, 2008 at 10:53 pm

    #900327

    steveb (11/18/2008)

    SELECT * FROM sys.database_principals

    WHERE TYPE = 's'

    thanks for knowledge updation

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply