List all Usernames, Roles for all the databases.

  • shivaram challa

    SSCrazy

    Points: 2415

    Comments posted to this topic are about the item List all Usernames, Roles for all the databases.

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • KDASQL

    Ten Centuries

    Points: 1103

    I get the following error:

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    (0 row(s) affected)

    Running on SQL 2005 Enterprise edition SP2

  • shivaram challa

    SSCrazy

    Points: 2415

    There were some miscellaneous characters in the below statement that was causing that issue.

    I have attached the image highlighting the code that have those characters in it.

    Also, I have updated the script on the site.

    Removed the spaces infront of the "u.name" from the below code section.

    EXEC sp_MSForEachdb

    '

    SELECT ''?'',

    u.name,

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • KDASQL

    Ten Centuries

    Points: 1103

    Thank you. It works now.

  • Key DBA

    SSCertifiable

    Points: 6029

    I get the following error when running the query against one of my servers:

    Msg 102, Level 15, State 1, Line 14

    Incorrect syntax near '-'.

    (260 row(s) affected)

    (260 row(s) affected)

    ... I have a user named 'dotnet-apps' that seems to be causing the problem. The result set still displays all the other users, just not the 'dotnet-apps' account.

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • KDASQL

    Ten Centuries

    Points: 1103

    Is there anyway we can make this work in SQL 2000. I need to run this on a SQL 2000 box.

    Please advise.

    Thank you.

  • shivaram challa

    SSCrazy

    Points: 2415

    Key DBA (8/28/2008)


    I get the following error when running the query against one of my servers:

    Msg 102, Level 15, State 1, Line 14

    Incorrect syntax near '-'.

    (260 row(s) affected)

    (260 row(s) affected)

    ... I have a user named 'dotnet-apps' that seems to be causing the problem. The result set still displays all the other users, just not the 'dotnet-apps' account.

    I am trying to test this scenario with a hypenated username, but SQL Server doesn't let me create it.

    CREATE LOGIN shiva-test

    WITH PASSWORD = 'test';

    how were you able to create it?

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • shivaram challa

    SSCrazy

    Points: 2415

    KDASQL (8/28/2008)


    Is there anyway we can make this work in SQL 2000. I need to run this on a SQL 2000 box.

    Please advise.

    Thank you.

    You can't run this script on SQL Server 2000 as the script is based on the catalog views which are newly introduced in SQL server 2005. This script can be changed to work on SQL 2000 fairly quickly using the below table mapping (source: SQL server 2005 books online).

    systable in 2000 = view in 2005

    -------------------------------

    syslogins = sys.server_principals

    sysusers = sys.database_principals

    sysmembers = sys.database_role_members

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • KDASQL

    Ten Centuries

    Points: 1103

    You can't run this script on SQL Server 2000 as the script is based on the catalog views which are newly introduced in SQL server 2005. This script can be changed to work on SQL 2000 fairly quickly using the below table mapping (source: SQL server 2005 books online).

    systable in 2000 = view in 2005

    -------------------------------

    syslogins = sys.server_principals

    sysusers = sys.database_principals

    sysmembers = sys.database_role_members

    Thank you.:)

  • CAGreensfelder

    SSC Eights!

    Points: 919

    Adapted for SQL 2000

    /**

    Script: list all Usernames, Roles for all the databases.

    Author: Shiva Challa (http://challa.info)

    and the database Roles that the user belongs to in all the databases.

    Also, you can use this script to get the roles of one user in all the databases.

    Directions of Use:

    For All Users list: You can directly run this script in SQL Server Management studio

    For a specific user:

    ????????1. Find this code and u.name like ''tester''

    ????????2. Uncomment the code

    ????????3. Replace the Name ''tester'' with the username you want to search on.

    Resultset:

    ????????DBName: Database name that the user exists in.

    ????????Name: user name.

    ????????GroupName: Group/Database Role that the user is a part of.

    ????????LoginName: Actual login name, if this is null, Name is used to connect.

    ????????default_database_name

    ????????default_schema_name????

    ????????principal_id

    ????????sid

    Change History:

    8/26/2008 Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.

    9/2/2008 Cathy Greenselder - Convert to SQL2000

    ????????(default_database_name not in SQL2K)

    ????????(default_schema_name????not in SQL2K)

    ????????(principal_id not in SQL2K)

    uid is in SQL2K

    **/

    USE MASTER

    GO

    BEGIN

    IF EXISTS (SELECT * FROM dbo.sysobjects

    WHERE id = OBJECT_ID(N'[dbo].#TUser')

    AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

    DROP TABLE #TUser

    CREATE TABLE #tuser (

    DBName VARCHAR(50),

    UserName SYSNAME,

    GroupName SYSNAME NULL,

    LoginName SYSNAME NULL,

    uid INT,

    sid VARBINARY(85))

    INSERT INTO #TUser

    EXEC sp_MSForEachdb

    '

    SELECT ''?'' as DBName,

    u.name As UserName,

    CASE

    WHEN (r.uid IS NULL) THEN ''public''

    ELSE r.name

    END AS GroupName,

    l.name AS LoginName,

    u.uid,

    u.sid

    FROM ?.dbo.sysUsers u

    LEFT JOIN (?.dbo.sysMembers m

    JOIN ?.dbo.sysUsers r

    ON m.memberuid = r.uid)

    ON m.memberuid = u.uid

    LEFT JOIN dbo.sysLogins l

    ON u.sid = l.sid

    WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1

    /*and u.name like ''tester''*/

    ORDER BY u.name

    '

    SELECT *

    FROM #TUser

    ORDER BY DBName,

    UserName,

    GroupName

    DROP TABLE #TUser

    END

  • shivaram challa

    SSCrazy

    Points: 2415

    Thanks Cathy !!

    Do you mind if I add this to the online script?

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • CAGreensfelder

    SSC Eights!

    Points: 919

    shiva challa (10/2/2008)


    Thanks Cathy !!

    Do you mind if I add this to the online script?

    Not at all. Please do. 🙂

    Cathy

  • shivaram challa

    SSCrazy

    Points: 2415

    Here is the updated script.

    I have also updated the online script, Just waiting for it to be published.

    /**

    Script: list all Usernames, Roles for all the databases.

    Author: Shiva Challa (http://challa.info)

    and the database Roles that the user belongs to in all the databases.

    Also, you can use this script to get the roles of one user in all the databases.

    Directions of Use:

    For All Users list: You can directly run this script in SQL Server Management studio

    For a specific user:

    ????????1. Find this code and u.name like ''tester''

    ????????2. Uncomment the code

    ????????3. Replace the Name ''tester'' with the username you want to search on.

    Resultset:

    ????????DBName: Database name that the user exists in.

    ????????Name: user name.

    ????????GroupName: Group/Database Role that the user is a part of.

    ????????LoginName: Actual login name, if this is null, Name is used to connect.

    ????????default_database_name

    ????????default_schema_name????

    ????????principal_id

    ????????sid

    Change History:

    08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.

    09/02/2008 Cathy Greenselder - Convert to SQL2000

    (default_database_name not in SQL2K)

    (default_schema_name not in SQL2K)

    (principal_id not in SQL2K)

    (uid is in SQL2K

    10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.

    **/

    USE MASTER

    GO

    BEGIN

    IF EXISTS (

    SELECT TOP 1 *

    FROM Tempdb.sys.objects (nolock)

    WHERE name LIKE '#TUser%')

    DROP TABLE #TUser

    CREATE TABLE #tuser (

    DBName VARCHAR(50),

    [Name] SYSNAME,

    GroupName SYSNAME NULL,

    LoginName SYSNAME NULL,

    default_database_name VARCHAR(50) NULL,

    default_schema_name VARCHAR(256) NULL,

    Principal_id INT,

    sid VARBINARY(85))

    IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'

    INSERT INTO #TUser

    EXEC sp_MSForEachdb

    '

    SELECT

    ''?'' as DBName,

    u.name As UserName,

    CASE

    WHEN (r.uid IS NULL) THEN ''public''

    ELSE r.name

    END AS GroupName,

    l.name AS LoginName,

    NULL AS Default_db_Name,

    NULL as default_Schema_name,

    u.uid,

    u.sid

    FROM ?.dbo.sysUsers u

    LEFT JOIN (?.dbo.sysMembers m

    JOIN ?.dbo.sysUsers r

    ON m.memberuid = r.uid)

    ON m.memberuid = u.uid

    LEFT JOIN dbo.sysLogins l

    ON u.sid = l.sid

    WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1

    /*and u.name like ''tester''*/

    ORDER BY u.name

    '

    ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'

    INSERT INTO #TUser

    EXEC sp_MSForEachdb '

    SELECT

    ''?'',

    u.name,

    CASE

    WHEN (r.principal_id IS NULL) THEN ''public''

    ELSE r.name

    END GroupName,

    l.name LoginName,

    l.default_database_name,

    u.default_schema_name,

    u.principal_id,

    u.sid

    FROM ?.sys.database_principals u

    LEFT JOIN (?.sys.database_role_members m

    JOIN ?.sys.database_principals r

    ON m.role_principal_id = r.principal_id)

    ON m.member_principal_id = u.principal_id

    LEFT JOIN ?.sys.server_principals l

    ON u.sid = l.sid

    WHERE u.TYPE <> ''R''

    /*and u.name like ''tester''*/

    order by u.name

    '

    SELECT *

    FROM #TUser

    ORDER BY DBName,

    [name],

    GroupName

    DROP TABLE #TUser

    END

    Kindest Regards,
    Shivaram Challa
    (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • KDASQL

    Ten Centuries

    Points: 1103

    CAGreensfelder (10/2/2008)


    Adapted for SQL 2000

    /**

    Script: list all Usernames, Roles for all the databases.

    Author: Shiva Challa (http://challa.info)

    and the database Roles that the user belongs to in all the databases.

    Also, you can use this script to get the roles of one user in all the databases.

    Directions of Use:

    For All Users list: You can directly run this script in SQL Server Management studio

    For a specific user:

    ????????1. Find this code and u.name like ''tester''

    ????????2. Uncomment the code

    ????????3. Replace the Name ''tester'' with the username you want to search on.

    Resultset:

    ????????DBName: Database name that the user exists in.

    ????????Name: user name.

    ????????GroupName: Group/Database Role that the user is a part of.

    ????????LoginName: Actual login name, if this is null, Name is used to connect.

    ????????default_database_name

    ????????default_schema_name????

    ????????principal_id

    ????????sid

    Change History:

    8/26/2008 Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.

    9/2/2008 Cathy Greenselder - Convert to SQL2000

    ????????(default_database_name not in SQL2K)

    ????????(default_schema_name????not in SQL2K)

    ????????(principal_id not in SQL2K)

    uid is in SQL2K

    **/

    USE MASTER

    GO

    BEGIN

    IF EXISTS (SELECT * FROM dbo.sysobjects

    WHERE id = OBJECT_ID(N'[dbo].#TUser')

    AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

    DROP TABLE #TUser

    CREATE TABLE #tuser (

    DBName VARCHAR(50),

    UserName SYSNAME,

    GroupName SYSNAME NULL,

    LoginName SYSNAME NULL,

    uid INT,

    sid VARBINARY(85))

    INSERT INTO #TUser

    EXEC sp_MSForEachdb

    '

    SELECT ''?'' as DBName,

    u.name As UserName,

    CASE

    WHEN (r.uid IS NULL) THEN ''public''

    ELSE r.name

    END AS GroupName,

    l.name AS LoginName,

    u.uid,

    u.sid

    FROM ?.dbo.sysUsers u

    LEFT JOIN (?.dbo.sysMembers m

    JOIN ?.dbo.sysUsers r

    ON m.memberuid = r.uid)

    ON m.memberuid = u.uid

    LEFT JOIN dbo.sysLogins l

    ON u.sid = l.sid

    WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1

    /*and u.name like ''tester''*/

    ORDER BY u.name

    '

    SELECT *

    FROM #TUser

    ORDER BY DBName,

    UserName,

    GroupName

    DROP TABLE #TUser

    END

    After I run the script the UserName and the GroupName are the same.

    Shouldn't the GroupName reflect which group the UserName is associated with....if username: xyz has dbo rights to a database shouldn't dbo show in the GroupName.

    Please advise.

    Thanks

  • KDASQL

    Ten Centuries

    Points: 1103

    shiva challa (10/8/2008)


    Here is the updated script.

    I have also updated the online script, Just waiting for it to be published.

    /**

    Script: list all Usernames, Roles for all the databases.

    Author: Shiva Challa (http://challa.info)

    and the database Roles that the user belongs to in all the databases.

    Also, you can use this script to get the roles of one user in all the databases.

    Directions of Use:

    For All Users list: You can directly run this script in SQL Server Management studio

    For a specific user:

    ????????1. Find this code and u.name like ''tester''

    ????????2. Uncomment the code

    ????????3. Replace the Name ''tester'' with the username you want to search on.

    Resultset:

    ????????DBName: Database name that the user exists in.

    ????????Name: user name.

    ????????GroupName: Group/Database Role that the user is a part of.

    ????????LoginName: Actual login name, if this is null, Name is used to connect.

    ????????default_database_name

    ????????default_schema_name????

    ????????principal_id

    ????????sid

    Change History:

    08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.

    09/02/2008 Cathy Greenselder - Convert to SQL2000

    (default_database_name not in SQL2K)

    (default_schema_name not in SQL2K)

    (principal_id not in SQL2K)

    (uid is in SQL2K

    10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.

    **/

    USE MASTER

    GO

    BEGIN

    IF EXISTS (SELECT TOP 1 *

    FROM Tempdb.sys.objects (nolock)

    WHERE name LIKE '#TUser%')

    DROP TABLE #TUser

    CREATE TABLE #tuser (

    DBName VARCHAR(50),

    [Name] SYSNAME,

    GroupName SYSNAME NULL,

    LoginName SYSNAME NULL,

    default_database_name VARCHAR(50) NULL,

    default_schema_name VARCHAR(256) NULL,

    Principal_id INT,

    sid VARBINARY(85))

    IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'

    INSERT INTO #TUser

    EXEC sp_MSForEachdb

    '

    SELECT

    ''?'' as DBName,

    u.name As UserName,

    CASE

    WHEN (r.uid IS NULL) THEN ''public''

    ELSE r.name

    END AS GroupName,

    l.name AS LoginName,

    NULL AS Default_db_Name,

    NULL as default_Schema_name,

    u.uid,

    u.sid

    FROM ?.dbo.sysUsers u

    LEFT JOIN (?.dbo.sysMembers m

    JOIN ?.dbo.sysUsers r

    ON m.memberuid = r.uid)

    ON m.memberuid = u.uid

    LEFT JOIN dbo.sysLogins l

    ON u.sid = l.sid

    WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1

    /*and u.name like ''tester''*/

    ORDER BY u.name

    '

    ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'

    INSERT INTO #TUser

    EXEC sp_MSForEachdb '

    SELECT

    ''?'',

    u.name,

    CASE

    WHEN (r.principal_id IS NULL) THEN ''public''

    ELSE r.name

    END GroupName,

    l.name LoginName,

    l.default_database_name,

    u.default_schema_name,

    u.principal_id,

    u.sid

    FROM ?.sys.database_principals u

    LEFT JOIN (?.sys.database_role_members m

    JOIN ?.sys.database_principals r

    ON m.role_principal_id = r.principal_id)

    ON m.member_principal_id = u.principal_id

    LEFT JOIN ?.sys.server_principals l

    ON u.sid = l.sid

    WHERE u.TYPE <> ''R''

    /*and u.name like ''tester''*/

    order by u.name

    '

    SELECT *

    FROM #TUser

    ORDER BY DBName,

    [name],

    GroupName

    DROP TABLE #TUser

    END

    Getting this error when I run this script in sql 2000:

    Server: Msg 208, Level 16, State 1, Line 4

    Invalid object name 'Tempdb.sys.objects'.

    Please advise.

    Thanks

Viewing 15 posts - 1 through 15 (of 61 total)

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