Below Script is used to find the Users in Database and their permissions
SQL Server 2000
use [master]
select db_name(0)AS DBName, a.name, CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'
WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'
WHEN a.issqlrole=1 THEN 'Database Role'
ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'
from master..syslogins sl join dbo.sysusers a on a.sid=sl.sid LEFT OUTER JOIN dbo.sysmembers b ON a.uid=b.memberuid where a.altuid<>1 and a.uid not in (1,2) AND 'DBName' NOT IN ('master','msdb','model','tempdb') ORDER BY a.Name
SQL Server 2008
use [master]
Select db_name(0) As DBName,a.name 'User_Name',
CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'
WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'
WHEN a.issqlrole=1 THEN 'Database Role'
ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID'
from master..syslogins sl join sysusers a on sl.sid=a.sid
LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid
--where a.altuid<>1
SQL Server 2008
use master
Select db_name(0) As DBName,a.name 'User_Name',
CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN 'Windows Group'
WHEN a.isntgroup =0 AND a.isntuser=1 THEN 'Windows Login'
WHEN a.issqlrole=1 THEN 'Database Role'
ELSE 'SQL Login' END AS 'Login Type',USER_NAME(b.groupuid) AS 'AssociatedRole' ,sl.name 'Login_ID',
default_schema_name
from master..syslogins sl join sysusers a on sl.sid=a.sid
LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid
left join sys.database_principals dp on dp.principal_id=a.uid
--where a.altuid<>1
Script to find User Permissions on all DB's in a SQL Server
create table #DBuserperm (DBName varchar(100),User_Name varchar(100),Login_Type varchar(100)
,Associated_Role varchar(100),LoginID varchar(100),Default_Schema_Name varchar(100),)
declare @dbname varchar(100)
declare @sqlQuery nvarchar(4000)
declare dbcursor CURSOR for
select name from sys.databases
where name not in ('master','model','msdb','tempdb')
OPEN dbcursor
FETCH NEXT FROM dbcursor
into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlQuery='Use ['+@dbname+']'
set @sqlQuery =@sqlQuery+ 'Select db_name(0) As DBName,a.name '''+'User_Name'+''',
CASE WHEN a.isntgroup =1 AND a.isntuser=0 THEN '''+'Windows Group'+'''
WHEN a.isntgroup =0 AND a.isntuser=1 THEN '''+'Windows Login'+'''
WHEN a.issqlrole=1 THEN '''+'Database Role'+'''
ELSE '''+'SQL Login'+''' END AS '''+'Login Type'+''',USER_NAME(b.groupuid) AS '''+'AssociatedRole'+''' ,
sl.name '''+'Login_ID'+''',
default_schema_name
from master..syslogins sl join sysusers a on sl.sid=a.sid
LEFT OUTER JOIN sysmembers b ON a.uid=b.memberuid
left join sys.database_principals dp on dp.principal_id=a.uid'
insert into #DBuserperm
exec sp_executesql @sqlQuery
FETCH NEXT FROM dbcursor
into @dbname
END
select * from #DBuserperm
drop table #DBuserperm
CLOSE dbcursor
Deallocate dbcursor
