Post reply

Get listing of all login accounts for an instance(Windows auth. and Sql Server auth.)

  • October 8, 2010 at 2:48 pm

    #241396

    How can you get a list of all the accounts for an instance?

    Is it also possible to get a listing of what accounts are currently logged in?

  • October 8, 2010 at 2:56 pm

    #1234545

    because windows groups can be granted access, and that includes people who potentially never logged into your instance yet, i think you have to query active directory for the win users; SQL users are in master.sys.logins.

    for who is logged in now, i would simply use sp_who or sp_who2.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 8, 2010 at 4:09 pm

    #1234567

    Thanks!

  • October 15, 2010 at 8:49 am

    #1237612

    You can try this at least to see who has access to what database, and with what permissions.

    IF EXISTS ( SELECT *

    FROM tempdb.dbo.sysobjects

    WHERE id = OBJECT_ID(N'[tempdb].[dbo].[SQL_DB_REP]') )

    DROP TABLE [tempdb].[dbo].[SQL_DB_REP] ;

    GO

    CREATE TABLE [tempdb].[dbo].[SQL_DB_REP]

    (

    [Server] [varchar](100) NOT NULL,

    [DB_Name] [varchar](70) NOT NULL,

    [User_Name] [nvarchar](90) NULL,

    [Group_Name] [varchar](100) NULL,

    [Account_Type] [varchar](22) NULL,

    [Login_Name] [varchar](80) NULL,

    [Def_DB] [varchar](100) NULL

    )

    ON [PRIMARY]

    INSERT INTO [tempdb].[dbo].[SQL_DB_REP]

    Exec sp_MSForEachDB 'SELECT

    CONVERT(varchar(100), SERVERPROPERTY(''Servername'')) AS Server,

    ''?'' AS DB_Name,usu.name u_name,

    CASEWHEN (usg.uid is null) THEN ''public''

    ELSE usg.name

    END as Group_Name,

    CASEWHEN usu.isntuser=1 THEN ''Windows Domain Account''

    WHEN usu.isntgroup = 1 THEN ''Windows Group''

    WHEN usu.issqluser = 1 THEN''SQL Account''

    WHEN usu.issqlrole = 1 THEN ''SQL Role''

    END as Account_Type,

    lo.loginname,

    lo.dbname AS Def_DB

    FROM

    [?]..sysusers usu LEFT OUTER JOIN

    ([?]..sysmembers mem INNER JOIN

    [?]..sysusers usg ON mem.groupuid = usg.uid)

    ON usu.uid = mem.memberuid LEFT OUTER JOIN

    master.dbo.syslogins lo ON usu.sid = lo.sid

    WHERE

    ( usu.islogin = 1 AND

    usu.isaliased = 0 AND

    usu.hasdbaccess = 1) AND

    (usg.issqlrole = 1 OR

    usg.uid is null)'

    Select *

    from [tempdb].[dbo].[SQL_DB_REP]

  • October 15, 2010 at 12:36 pm

    #1237803

    Wow! Very cool. Thank you.

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

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