August 12, 2011 at 7:22 am
Hi Team,
we around 20 instances Each instace 20 databases is there
now i want Each Database user name and database name
OUT PUT :
usename DatabaseName
0aaaa zz
0bbb zz
0ccc zz
9aaa yy
9bbb yy
In this way all 20 database information i want can you any body please provide the script for me
it really helpful to me
Note: users with out duplicate
Thanks in Advance
August 12, 2011 at 7:33 am
sp_msForEachDB ' SELECT ''?'' AS DBName, name As UserName from ?.sys.database_principals'
Lowell
August 12, 2011 at 7:34 am
WITH Users AS (
SELECT 'DB1' DBName, [name] FROM DB1.sys.database_principals UNION ALL
SELECT 'DB2' DBName, [name] FROM DB2.sys.database_principals UNION ALL
SELECT 'DB3' DBName, [name] FROM DB3.sys.database_principals UNION ALL
SELECT 'DB4' DBName, [name] FROM DB4.sys.database_principals UNION ALL
SELECT 'DB5' DBName, [name] FROM DB5.sys.database_principals UNION ALL
SELECT 'DB6' DBName, [name] FROM DB6.sys.database_principals UNION ALL
SELECT 'DB7' DBName, [name] FROM DB7.sys.database_principals UNION ALL
SELECT 'DB8' DBName, [name] FROM DB8.sys.database_principals UNION ALL
SELECT 'DB9' DBName, [name] FROM DB9.sys.database_principals UNION ALL
SELECT 'DB10' DBName, [name] FROM DB10.sys.database_principals UNION ALL
SELECT 'DB11' DBName, [name] FROM DB11.sys.database_principals UNION ALL
SELECT 'DB12' DBName, [name] FROM DB12.sys.database_principals UNION ALL
SELECT 'DB13' DBName, [name] FROM DB13.sys.database_principals UNION ALL
SELECT 'DB14' DBName, [name] FROM DB14.sys.database_principals UNION ALL
SELECT 'DB15' DBName, [name] FROM DB15.sys.database_principals UNION ALL
SELECT 'DB16' DBName, [name] FROM DB16.sys.database_principals UNION ALL
SELECT 'DB17' DBName, [name] FROM DB17.sys.database_principals UNION ALL
SELECT 'DB18' DBName, [name] FROM DB18.sys.database_principals UNION ALL
SELECT 'DB19' DBName, [name] FROM DB19.sys.database_principals UNION ALL
SELECT 'DB20' DBName, [name] FROM DB20.sys.database_principals
)
SELECT DBName, [name] FROM Users
Note: users with out duplicate
Say that again but slower? You can't have the same user twice in the same database. If you mean you only want the same user name to appear once no matter how many databases it appears in, which database name do you want to be displayed next to it?
John
August 12, 2011 at 7:41 am
hi Lowell/John,
1st is working but it will not display all the database please suggest me.
it is missing some database and i want only two cols it wil displaying one database after another one
2nd one i m getting below error
Msg 208, Level 16, State 1, Line 2
Invalid object name 'DB1.sys.database_principals'.
In the DB1 i have to put my dbname right or correct me
tx
August 12, 2011 at 7:45 am
shiv-356842 (8/12/2011)
hi John,i m getting below error
Msg 208, Level 16, State 1, Line 2
Invalid object name 'DB1.sys.database_principals'.
1st is working it will not display all the database please suggest me.
tx
The error is saying you don't have a database named 'DB1'. Just replace the DBn with your database names.
HTH,
Rob
August 12, 2011 at 7:45 am
Surely you don't need me to explain this to you? Read the error message. There's no such object as DB1.sys.principals. Why? Because there's no such database as DB1. So, if your databases really aren't called DB1 to DB20, try inserting the actual names of the databases.
John
August 12, 2011 at 8:02 am
Hi John,
Sorry my reply..
as per your suggestion i have changed my dbname
but i m getting below error :
Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 2 in SELECT statement.
August 12, 2011 at 8:06 am
Probably because your databases don't all have the same collation. Choose a collation that will make all the database names distinct, and specify it in every SELECT statement in the CTE, something like this:
WITH Users AS (
SELECT 'DB1' DBName, [name] COLLATE Latin1_General_CS_AS FROM DB1.sys.database_principals UNION ALL
SELECT 'DB2' DBName, [name] COLLATE Latin1_General_CS_AS FROM DB2.sys.database_principals UNION ALL
SELECT 'DB3' DBName, [name] COLLATE Latin1_General_CS_AS FROM DB3.sys.database_principals UNION ALL
SELECT 'DB4' DBName, [name] COLLATE Latin1_General_CS_AS FROM DB4.sys.database_principals UNION ALL
...
SELECT 'DB20' DBName, [name] COLLATE Latin1_General_CS_AS FROM DB20.sys.database_principals
)
SELECT DBName, [name] FROM Users
John
August 12, 2011 at 8:19 am
Hi John
Sorry for my reply..actully im oracle guy please help me
Again i m getting bleow error
No column name was specified for column 2 of 'Users'.
August 12, 2011 at 8:27 am
Again, the answer is in the error message. Just put a column alias after the collation name in each SELECT statement, the same way I did for the "DBName" column. Call it UserName or something like that. Then don't forget to change the second column in the final SELECT statement to the same name. Also, you may wish to add WHERE [type] <> 'R' at the end of each SELECT statement if you don't want to see roles as well as users.
John
August 12, 2011 at 8:36 am
Hi Team
In this way right
SELECT 'xx' [DBName]COLLATE Latin1_General_CS_AS, [name] COLLATE Latin1_General_CS_AS FROM xx.sys.database_principals
or
WITH Users AS (
SELECT 'master' [DBName], [name] COLLATE Latin1_General_CS_AS FROM Master.sys.database_principals where type <> 'R' UNION ALL
SELECT 'Model' [DBName], [name] COLLATE Latin1_General_CS_AS FROM Model.sys.database_principals where type <> 'R'
)
SELECT DBName, [name] FROM Users
please give correct script.. and correct me
Tx
August 12, 2011 at 8:45 am
shiv-356842 (8/12/2011)
In this way right
Does either one work? That's usually a good test. I don't think they will, since the error message you were getting related to column 2, and you haven't done anything about that. Like I said, put the column alias after the collation name.
John
August 12, 2011 at 8:51 am
WITH Users AS (
SELECT 'Master' [DBName]a, [name] b COLLATE Latin1_General_CS_AS FROM Master.sys.database_principals where type <> 'R' UNION ALL
SELECT 'Model' [DBName] a, [name]b COLLATE Latin1_General_CS_AS FROM Model.sys.database_principals where type <> 'R'
)
SELECT DBName, [name] FROM Users
In this way right
August 12, 2011 at 9:01 am
Does it work? If it doesn't, it's not right. After the collation name is the bit you need to pay attention to. And don't forget to change your final select statement accordingly.
John
August 12, 2011 at 9:03 am
It isn't going to work like that at all. You have now defined two aliases for the first column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply