locked
Database User or Server user Findout. RRS feed

  • Question

  • Hi Team,

     

    How can we find out  ' F012345' user is belogns to Database User  or Server User  please suggest me.

     

    Tx


    subu
    Tuesday, August 9, 2011 12:47 PM

Answers

  • Hallo Subu,

    if you want to drop a LOGIN you have to check whether a corresponding USER exists in the database, isn't it?
    You can compare server principals and database principals by usiing the system views.

    SELECT	s.name,
    		d.name
    FROM	sys.server_principals s LEFT JOIN dbcustomer.sys.database_principals d
    ON (s.principal_id = d.principal_id)
    WHERE	s.name = 'test'
    

    Please note the following:

    - do not compare names because the name of a login can deferre from the name in the database
    - you have to check it for every database (maybe you can use a cursor for the collection)


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    • Proposed as answer by Peja Tao Thursday, August 11, 2011 5:29 AM
    • Marked as answer by Peja Tao Tuesday, August 16, 2011 8:08 AM
    Wednesday, August 10, 2011 8:36 AM

All replies

  • Hi,

    I'm not sure I totally understand your question.  You can view a list of SQL Server logins by connecting to your instance, then:

    - Expand Security > Logins

    This will show you a list of logins that could represent an AD user, AD group, SQL Login, etc.  Each login could map to a user in 1 or more databases.  If you double click on the login, and select the 'User Mapping' page, you can see which database(s) the login maps to.  Each db user, although tied to the same login, can have varying levels of permissions.

    From a database perspective, you can see the list of database users by:

    - Expand the database > security > users

    Hope this helps,

    Andrew

    Tuesday, August 9, 2011 1:14 PM
  • Hi Thank you for  reply..

     

    i want drop user in the server level and database level using command level..

     

    Tx


    subu
    Tuesday, August 9, 2011 1:33 PM
  • Hi,

    Against each database with the user you want to drop, you need to run: DROP USER username

    Then you can drop login, i.e. DROP LOGIN login_name

    Andrew

    Tuesday, August 9, 2011 2:28 PM
  • Hallo Subu,

    if you want to drop a LOGIN you have to check whether a corresponding USER exists in the database, isn't it?
    You can compare server principals and database principals by usiing the system views.

    SELECT	s.name,
    		d.name
    FROM	sys.server_principals s LEFT JOIN dbcustomer.sys.database_principals d
    ON (s.principal_id = d.principal_id)
    WHERE	s.name = 'test'
    

    Please note the following:

    - do not compare names because the name of a login can deferre from the name in the database
    - you have to check it for every database (maybe you can use a cursor for the collection)


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    • Proposed as answer by Peja Tao Thursday, August 11, 2011 5:29 AM
    • Marked as answer by Peja Tao Tuesday, August 16, 2011 8:08 AM
    Wednesday, August 10, 2011 8:36 AM
  • Hi Subu,

    You can use

    sp_helpuser 'F012345'

    sp_helplogins ''F012345'

    For Server Role membership, you can use below code:

     

    DECLARE @login sysname

      SET @login= 'F012345'
        
      SELECT SUSER_NAME(SRoleMembers.role_principal_id) AS [Role]
      FROM sys.server_role_members as SRoleMembers
      WHERE SUSER_NAME(SRoleMembers.member_principal_id) = @login
    For Database Role membership, you can use below code:
    DECLARE @username sysname 
    SET @username = 'F012345'; 
     -- USE a Common Table Expression to build a recursive list 
     -- of roles for which this user is a member 
     WITH UserDBRoles (role_principal_id) 
     AS
     ( 
       SELECT DBRoleMembers.role_principal_id 
       FROM sys.database_role_members AS DBRoleMembers 
       WHERE USER_NAME(DBRoleMembers.member_principal_id) = @username 
       UNION ALL
         SELECT DBRoleMembers.role_principal_id 
         FROM sys.database_role_members AS DBRoleMembers 
        INNER JOIN UserDBRoles ON DBRoleMembers.member_principal_id = UserDBRoles.role_principal_id 
     
    SELECT USER_NAME(role_principal_id) [Role] 
    FROM UserDBRoles
    To Drop any user or login:
    Right click on that user or login and select Delete or
    you can use below code
    Drop user <username>
    Drop login <loginname>
    Hope this helps!!

    Regards, Vishal Srivastava
    Wednesday, August 10, 2011 8:53 AM
  • Hallo Vishalri09,

    he wants to know in which database a login has an useraccount but not the database roles!


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITS Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    Wednesday, August 10, 2011 9:31 AM