Friday, January 13, 2012

List all the users of a particular database.

Run below query to get list of all the users of a particular Database for SQL Server 2005 and later
USE <Database_Name>

SELECT suser_sname(SID) AS ServerLevelLogin, 
       Name AS  DatabaseLevelUser, 
FROM sys.database_principals 
WHERE Principal_ID >= 5 and IS_Dixed_Role <> 1
In SQL 2000 run below query:
SELECT ISNULL(suser_sname(SID), 'No Mapping') AS ServerLevelLogin, 
       Name AS DatabaseLevelUser, 
       WHEN islogin = 1 then 'Login'
       ELSE 'Role'
       END AS Type_Desc, 
       WHEN issqluser = 1 then 'SQL User'
       ELSE 'Windows User\Group'
       END AS Type_Desc2,
FROM SysUsers
WHERE Name Not Like '[dpg][bu][_obe]%'

No comments:

Post a Comment