Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

List all Usernames, Roles for all the databases. Expand / Collapse
Author
Message
Posted Thursday, July 24, 2008 9:45 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
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
Post #540661
Posted Monday, August 25, 2008 2:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 11:04 AM
Points: 33, Visits: 220
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
Post #558465
Posted Monday, August 25, 2008 11:26 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
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


  Post Attachments 
errorChar.GIF (43 views, 7.98 KB)
Post #558594
Posted Tuesday, August 26, 2008 12:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 11:04 AM
Points: 33, Visits: 220
Thank you. It works now.

Post #558609
Posted Thursday, August 28, 2008 8:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 26, 2009 3:02 PM
Points: 515, 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
Post #560512
Posted Thursday, August 28, 2008 7:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 11:04 AM
Points: 33, Visits: 220
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.
Post #560930
Posted Thursday, September 25, 2008 8:40 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
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
Post #576058
Posted Thursday, September 25, 2008 9:14 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
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
Post #576104
Posted Thursday, September 25, 2008 9:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 11:04 AM
Points: 33, Visits: 220
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.:)
Post #576143
Posted Thursday, October 2, 2008 7:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 16, 2012 7:34 PM
Points: 59, 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


Post #579654
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse