locked
how can i find the list of sql server logins with users and logins roles and etc... RRS feed

  • Question

  • Hi All,

    i want to gather the information about the all logins with user maping information and permissions through the script.

    Thansk in Advance,


    rup

    Monday, August 13, 2012 8:29 PM

Answers

  • Check : http://mssqlfun.wordpress.com/2012/07/18/genrate-database-user-script-with-access-rights/

    Try below script for the same...

    SET NOCOUNT ON
    GO

    CREATE TABLE #DBROLE (DBNAME VARCHAR(100), DBROLE VARCHAR (100), DBROLE_MEMBER VARCHAR(100),
     SID VARBINARY(85), DEFAULT_SCHEMA_NAME VARCHAR(100), LOGIN_NAME VARCHAR(100), DB_PRINCIPAL_ID INT)
     
    INSERT INTO #DBROLE
     SELECT DB_NAME() DBNAME, C.NAME, B.NAME, B.SID, B.DEFAULT_SCHEMA_NAME, D.NAME LOGINNAME, B.PRINCIPAL_ID AS LOGIN_NAME
     FROM SYS.DATABASE_ROLE_MEMBERS A
     INNER JOIN SYS.DATABASE_PRINCIPALS B ON A.MEMBER_PRINCIPAL_ID = B.PRINCIPAL_ID
     INNER JOIN SYS.DATABASE_PRINCIPALS C ON A.ROLE_PRINCIPAL_ID = C.PRINCIPAL_ID
     LEFT JOIN SYS.SERVER_PRINCIPALS D ON B.SID = D.SID  where B.NAME <> 'dbo'

    ALTER TABLE #DBROLE ADD ID INT IDENTITY(1,1)

    DECLARE @COUNTER INT, @MAXID2 INT, @LOGIN_DBROLE VARCHAR(MAX)
    SELECT @MAXID2 = MAX(ID) FROM #DBROLE
    SET @COUNTER = 1
     
    WHILE @COUNTER <= @MAXID2
    BEGIN

    SELECT @LOGIN_DBROLE  = 'USE ['+DBNAME+']
    GO
    IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = '''+DBROLE_MEMBER+''')
    BEGIN
              CREATE USER ['+DBROLE_MEMBER+']
        FOR LOGIN ['+LOGIN_NAME+']'+ISNULL(' WITH DEFAULT_SCHEMA=['+DEFAULT_SCHEMA_NAME+']','')+'
    END
    ALTER USER ['+DBROLE_MEMBER+'] WITH LOGIN = ['+LOGIN_NAME+']
    EXEC SP_ADDROLEMEMBER '''+DBROLE+''','''+DBROLE_MEMBER+'''
    GO
    ' FROM #DBROLE WHERE ID = @COUNTER

    SELECT @COUNTER = @COUNTER + 1
    PRINT @LOGIN_DBROLE

    END

    DROP TABLE #DBROLE


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    • Edited by RohitGarg Monday, August 13, 2012 9:56 PM
    • Marked as answer by amber zhang Monday, August 20, 2012 2:02 AM
    Monday, August 13, 2012 9:55 PM
  • Hello Rup,

    Please also have a look at TechNet ScriptCenter:
    List all Server Permissions
    List all Database Permissions


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by amber zhang Monday, August 20, 2012 2:02 AM
    Tuesday, August 14, 2012 4:50 AM

All replies

  • Check : http://mssqlfun.wordpress.com/2012/07/18/genrate-database-user-script-with-access-rights/

    Try below script for the same...

    SET NOCOUNT ON
    GO

    CREATE TABLE #DBROLE (DBNAME VARCHAR(100), DBROLE VARCHAR (100), DBROLE_MEMBER VARCHAR(100),
     SID VARBINARY(85), DEFAULT_SCHEMA_NAME VARCHAR(100), LOGIN_NAME VARCHAR(100), DB_PRINCIPAL_ID INT)
     
    INSERT INTO #DBROLE
     SELECT DB_NAME() DBNAME, C.NAME, B.NAME, B.SID, B.DEFAULT_SCHEMA_NAME, D.NAME LOGINNAME, B.PRINCIPAL_ID AS LOGIN_NAME
     FROM SYS.DATABASE_ROLE_MEMBERS A
     INNER JOIN SYS.DATABASE_PRINCIPALS B ON A.MEMBER_PRINCIPAL_ID = B.PRINCIPAL_ID
     INNER JOIN SYS.DATABASE_PRINCIPALS C ON A.ROLE_PRINCIPAL_ID = C.PRINCIPAL_ID
     LEFT JOIN SYS.SERVER_PRINCIPALS D ON B.SID = D.SID  where B.NAME <> 'dbo'

    ALTER TABLE #DBROLE ADD ID INT IDENTITY(1,1)

    DECLARE @COUNTER INT, @MAXID2 INT, @LOGIN_DBROLE VARCHAR(MAX)
    SELECT @MAXID2 = MAX(ID) FROM #DBROLE
    SET @COUNTER = 1
     
    WHILE @COUNTER <= @MAXID2
    BEGIN

    SELECT @LOGIN_DBROLE  = 'USE ['+DBNAME+']
    GO
    IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = '''+DBROLE_MEMBER+''')
    BEGIN
              CREATE USER ['+DBROLE_MEMBER+']
        FOR LOGIN ['+LOGIN_NAME+']'+ISNULL(' WITH DEFAULT_SCHEMA=['+DEFAULT_SCHEMA_NAME+']','')+'
    END
    ALTER USER ['+DBROLE_MEMBER+'] WITH LOGIN = ['+LOGIN_NAME+']
    EXEC SP_ADDROLEMEMBER '''+DBROLE+''','''+DBROLE_MEMBER+'''
    GO
    ' FROM #DBROLE WHERE ID = @COUNTER

    SELECT @COUNTER = @COUNTER + 1
    PRINT @LOGIN_DBROLE

    END

    DROP TABLE #DBROLE


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    • Edited by RohitGarg Monday, August 13, 2012 9:56 PM
    • Marked as answer by amber zhang Monday, August 20, 2012 2:02 AM
    Monday, August 13, 2012 9:55 PM
  • Hello Rup,

    Please also have a look at TechNet ScriptCenter:
    List all Server Permissions
    List all Database Permissions


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by amber zhang Monday, August 20, 2012 2:02 AM
    Tuesday, August 14, 2012 4:50 AM
  • Hi Rupc,

    Please check below KB article

    http://support.microsoft.com/kb/918992

    Tuesday, August 14, 2012 4:59 AM