January 28, 2008 at 7:29 am
Hello everyone,
What would be the better way to dynamically get the user name of my login in a database other than the database where I am positionned.
Lets say the login L (my login name) have acces to database D and E.
The Login L is user name X in database D and user name Y in database E (but I don't know that).
I am currently in database X.
I can use USER or user_name() to get the user name X (in the current database) but how can I get user name Y being in database D?
Best regards,
Carl
January 28, 2008 at 9:32 am
sp_helplogins
January 28, 2008 at 9:39 am
Thank's David,
this solution requires the login to have sysadmin and securityadmin fixed server roles.
Not my case. 🙁
Anything else?
Best regards.
Carl
January 28, 2008 at 10:21 am
If you are unable to run this select statement because of permissions, turn this into a stored procedure and then get execute permissions on the procedure if you can.
DECLARE @name sysname,
@db sysname,
@query nvarchar(1000),
@counter int
IF OBJECT_ID('tempdb..#Pool') IS NOT NULL DROP TABLE #Pool
SELECT IDENTITY (int , 1, 1 ) AS PID,
CATALOG_NAME,
SCHEMA_OWNER
INTO #Pool
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN ('tempdb', 'model')
SET @counter = SCOPE_IDENTITY()
-- Loop through each database name
WHILE @counter > 0
BEGIN
SELECT @name = CATALOG_NAME + '.' + SCHEMA_OWNER + '.sysusers',
@db = CATALOG_NAME
FROM #Pool
WHERE PID = @counter
SELECT @query = 'SELECT ' + QUOTENAME(@db, '''') + 'as ' + QUOTENAME('DB', '''') +
', sx.name ' + QUOTENAME('login', '''') +
', su.name ' + QUOTENAME('user', '''') +
' FROM master.dbo.sysxlogins sx' +
' JOIN ' + @name + ' su' +
' ON sx.sid = su.sid'
EXEC (@query)
SET @counter = @counter - 1
END
DROP TABLE #Pool
January 28, 2008 at 11:13 am
Thanks a lot David,
Here is what I will use:
[font="Courier New"]IF OBJECT_ID('tempdb..##MyOtherUserName') IS NOT NULL
DROP TABLE ##MyOtherUserName
CREATE TABLE ##MyOtherUserName(UName varchar(30))
DECLARE @stmt nvarchar(500)
SET @stmt = 'use E'+char(10)+'INSERT INTO ##MyOtherUserName select user'
EXECUTE sp_executesql @stmt
SELECT * FROM ##MyOtherUserName[/font]
Best regards.
Carl
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply