Search

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, 
       Principal_ID, 
       Type_Desc, 
       Create_Date, 
       Modify_Date 
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, 
       CASE
       WHEN islogin = 1 then 'Login'
       ELSE 'Role'
       END AS Type_Desc, 
       CASE
       WHEN issqluser = 1 then 'SQL User'
       ELSE 'Windows User\Group'
       END AS Type_Desc2,
       CreateDate, 
       UpdateDate 
FROM SysUsers
WHERE Name Not Like '[dpg][bu][_obe]%'

No comments:

Post a Comment