Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: Query Server Instance for All Users and Access

    [SQL Server 2000] Before I reinvent the wheel, is there a built-in stored procedure in master that will return query results for all user accounts on the server instance, along with the database(s) they have access to and their level of access (or roles) within each database?
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    run sp_helpuser in each database, and sp_helpsrvrolemember for the server. Not perfect, but may be close enough,

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Here's a little thing I found, but made some changes to.

    -- SELECT * FROM DBROLES

    CREATE procedure Get_List_of_dbroles
    as
    declare @dbname varchar(200)
    declare @mSql1 varchar(8000)

    DECLARE DBName_Cursor CURSOR FOR
    select name
    from master.dbo.sysdatabases
    where name not in ('mssecurity','tempdb','master', 'northwind', 'pubs', 'msdb','model')
    Order by name

    truncate table DBRoles

    OPEN DBName_Cursor

    FETCH NEXT FROM DBName_Cursor INTO @dbname

    WHILE @@FETCH_STATUS = 0
    BEGIN


    Set @mSQL1 = 'Insert into DBROLES ( DBName, UserName, db_owner, db_accessadmin,
    db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
    db_denydatareader, db_denydatawriter, Login_Disabled, Server_Login )
    select DBName, UserName, db_owner, db_accessadmin,
    db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
    db_denydatareader, db_denydatawriter, Login_Disabled, Server_Login
    from
    (
    SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
    Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
    Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
    Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
    Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
    Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
    Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
    Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
    Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter,
    case when min(denylogin) = 1 then ''Yes'' else ''No'' end as Login_Disabled,
    case when min(m.sid) is null then ''No'' else ''Yes'' end as Server_Login
    from (
    select b.sid, b.name as USERName, c.name as RoleName
    from ' + @dbName+'.dbo.sysusers b '+char(13)+
    ' left join '+ @dbName+'.dbo.sysmembers a '+char(13)+
    ' on a.memberuid = b.uid left join '+@dbName +'.dbo.sysusers c
    on a.groupuid = c.uid where (b.issqlrole != 1) and (b.sid is not null)
    )s left join master..syslogins m on s.sid = m.sid
    Group by USERName
    union
    SELECT '+''''+@dbName +''''+ ' as DBName,
    Name as UserName,
    ''Yes'' as db_owner,
    ''Yes'' as db_accessadmin,
    ''Yes'' as db_securityadmin,
    ''Yes'' as db_ddladmin,
    ''Yes'' as db_datareader,
    ''Yes'' as db_datawriter,
    ''Yes'' as db_denydatareader,
    ''Yes'' as db_denydatawriter,
    ''No'' as Login_Disabled,
    ''sa-equiv'' as Server_Login
    from master..syslogins sl where sysadmin = 1
    and denylogin != 1 and not exists (select sid from '+@dbName +'..sysusers where sid = sl.sid) ) a
    order by UserName'

    --Print @mSql1
    Execute (@mSql1)

    FETCH NEXT FROM DBName_Cursor INTO @dbname
    END

    CLOSE DBName_Cursor
    DEALLOCATE DBName_Cursor

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    you'll also need this

    USE master
    go
    CREATE TABLE dbo.DBROLES
    (
    DBName sysname NOT NULL,
    UserName sysname NULL,
    db_owner varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    db_accessadmin varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    db_securityadmin varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    db_ddladmin varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    db_datareader varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    db_datawriter varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    db_denydatareader varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    db_denydatawriter varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    Login_Disabled varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    Server_Login varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )
    go
    IF OBJECT_ID('dbo.DBROLES') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.DBROLES >>>'
    ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.DBROLES >>>'
    go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •