August 12, 2008 at 10:14 am
I have deleted a user from the main Security -> login node but I need to make sure that user isnt in any other database login on the server, is there a T-SQL statement that I can use that will check all databases for this user?
August 12, 2008 at 10:35 am
Here's a script that will list databases and database roles for a particular user (replace YOURUSERHERE with the user you're searching for):
set nocount on
declare @name sysname,
@SQL nvarchar(600)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))
drop table #tmpTable
CREATE TABLE #tmpTable (
[DATABASENAME] sysname NOT NULL ,
[USER_NAME] sysname NOT NULL,
[ROLE_NAME] sysname NULL,
[LOGIN_NAME] sysname NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
select @SQL =
'insert into #tmpTable
select N'''+ @name + ''', a.name, c.name, d.name
from ' + QuoteName(@name) + '.dbo.sysusers a
left join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid
left join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid
left join ' + 'master.dbo.syslogins d on d.sid = a.sid
where a.name <> ''dbo'' and a.issqlrole = 0
and a.name = ''YOURUSERHERE'''
/* Insert row for each database */
execute (@SQL)
fetch c1 into @name
end
close c1
deallocate c1
select * from #tmpTable
Greg
August 12, 2008 at 10:44 am
Worked perfect thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply