SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


List all Usernames, Roles for all the databases.


List all Usernames, Roles for all the databases.

Author
Message
shivaram challa
shivaram challa
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 343
Comments posted to this topic are about the item List all Usernames, Roles for all the databases.

Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
KDASQL
KDASQL
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 221
I get the following error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

(0 row(s) affected)

Running on SQL 2005 Enterprise edition SP2
shivaram challa
shivaram challa
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 343
There were some miscellaneous characters in the below statement that was causing that issue.
I have attached the image highlighting the code that have those characters in it.

Also, I have updated the script on the site.

Removed the spaces infront of the "u.name" from the below code section.

EXEC sp_MSForEachdb
'
SELECT ''?'',
u.name,



Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Attachments
errorChar.GIF (67 views, 7.00 KB)
KDASQL
KDASQL
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 221
Thank you. It works now.
Key DBA
Key DBA
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2793 Visits: 655
I get the following error when running the query against one of my servers:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '-'.

(260 row(s) affected)

(260 row(s) affected)

... I have a user named 'dotnet-apps' that seems to be causing the problem. The result set still displays all the other users, just not the 'dotnet-apps' account.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
KDASQL
KDASQL
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 221
Is there anyway we can make this work in SQL 2000. I need to run this on a SQL 2000 box.

Please advise.

Thank you.
shivaram challa
shivaram challa
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 343
Key DBA (8/28/2008)
I get the following error when running the query against one of my servers:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '-'.

(260 row(s) affected)

(260 row(s) affected)

... I have a user named 'dotnet-apps' that seems to be causing the problem. The result set still displays all the other users, just not the 'dotnet-apps' account.



I am trying to test this scenario with a hypenated username, but SQL Server doesn't let me create it.
CREATE LOGIN shiva-test
WITH PASSWORD = 'test';



how were you able to create it?

Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
shivaram challa
shivaram challa
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1133 Visits: 343
KDASQL (8/28/2008)
Is there anyway we can make this work in SQL 2000. I need to run this on a SQL 2000 box.

Please advise.

Thank you.


You can't run this script on SQL Server 2000 as the script is based on the catalog views which are newly introduced in SQL server 2005. This script can be changed to work on SQL 2000 fairly quickly using the below table mapping (source: SQL server 2005 books online).

systable in 2000 = view in 2005
-------------------------------
syslogins = sys.server_principals
sysusers = sys.database_principals
sysmembers = sys.database_role_members



Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
KDASQL
KDASQL
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 221
You can't run this script on SQL Server 2000 as the script is based on the catalog views which are newly introduced in SQL server 2005. This script can be changed to work on SQL 2000 fairly quickly using the below table mapping (source: SQL server 2005 books online).

systable in 2000 = view in 2005
-------------------------------
syslogins = sys.server_principals
sysusers = sys.database_principals
sysmembers = sys.database_role_members


Thank you.Smile
CAGreensfelder
CAGreensfelder
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 375
Adapted for SQL 2000


/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
        1. Find this code and u.name like ''tester''
        2. Uncomment the code
        3. Replace the Name ''tester'' with the username you want to search on.

Resultset:
        DBName: Database name that the user exists in.
        Name: user name.
        GroupName: Group/Database Role that the user is a part of.
        LoginName: Actual login name, if this is null, Name is used to connect.
        default_database_name
        default_schema_name    
        principal_id
        sid

Change History:
8/26/2008 Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.


9/2/2008 Cathy Greenselder - Convert to SQL2000
        (default_database_name not in SQL2K)
        (default_schema_name    not in SQL2K)
        (principal_id not in SQL2K)
uid is in SQL2K

**/

USE MASTER
GO

BEGIN

IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].#TUser')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE #TUser

CREATE TABLE #tuser (
DBName VARCHAR(50),
UserName SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
uid INT,
sid VARBINARY(85))

INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT ''?'' as DBName,
u.name As UserName,
CASE
WHEN (r.uid IS NULL) THEN ''public''
ELSE r.name
END AS GroupName,
l.name AS LoginName,
u.uid,
u.sid
FROM ?.dbo.sysUsers u
LEFT JOIN (?.dbo.sysMembers m
JOIN ?.dbo.sysUsers r
ON m.memberuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
/*and u.name like ''tester''*/
ORDER BY u.name
'

SELECT *
FROM #TUser
ORDER BY DBName,
UserName,
GroupName

DROP TABLE #TUser
END



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search