logins
-
hi,
how to check whether a particular login owns objects and is as users looping through all databases.
Thanks.
-
Natalie hi,
Do you mean something like that:
--------------------------------------------------------------
CREATE TABLE #User_Objects (
DB sysname,
Obj_name sysname,
Type sysname 
INSERT #User_Objects
Exec sp_MSforeachdb
'SELECT ''?'' AS DB, SO.NAME, SO.TYPE
FROM ?..SYSOBJECTS SO
JOIN ?..SYSUSERS SU ON SU.UID = SO.UID
AND SU.NAME = ''USER_NAME'''
SELECT *
FROM #User_Objects
ORDER BY 1
DROP TABLE #User_Objects
--------------------------------------------------------------
1. Change 'USER_NAME' to your user name
2. You can remove temp table
3. you can put this script in procedure with USER_NAME as input parameter
-
Hi,
thnx for the code......
i came across another syntax too :
declare
@login sysname
declare @dbname sysname
set
@login = 'loginname'
select
@dbname = min(name) from master.dbo.sysdatabases
while @dbname is not null
begin
select @cmd = 'use '+@dbname+ ' declare @uid int, @cmd varchar(3000), @name sysname, @type char(2) '+ ' if exists (select * from sysusers where sid = suser_sid('''+@login+''')) '+'begin select @uid = uid, @name =name from sysusers where sid = suser_sid('''+@login+''') ' +'if exists (select * from sysobjects where uid = 1 and name in (select name from sysobjects where uid = @uid)) begin select @name = name, @type = type from sysobjects where uid = @uid ' +' print ''done processing'' print ''obj ''+@name+ '' of type '' +@type end end'
exec (@cmd)
select @dbname = min(name) from master.dbo.sysdatabases where name > @dbname
end
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply