July 24, 2008 at 9:45 pm
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[/url]
August 25, 2008 at 2:35 pm
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
August 25, 2008 at 11:26 pm
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[/url]
August 26, 2008 at 12:08 am
Thank you. It works now.
August 28, 2008 at 8:29 am
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
August 28, 2008 at 7:01 pm
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.
September 25, 2008 at 8:40 am
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[/url]
September 25, 2008 at 9:14 am
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[/url]
September 25, 2008 at 9:51 am
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.:)
October 2, 2008 at 7:52 am
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_namenot in SQL2K)
????????(default_schema_name????not in SQL2K)
????????(principal_idnot in SQL2K)
uidis 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
October 2, 2008 at 2:44 pm
Thanks Cathy !!
Do you mind if I add this to the online script?
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[/url]
October 8, 2008 at 6:57 am
shiva challa (10/2/2008)
Thanks Cathy !!Do you mind if I add this to the online script?
Not at all. Please do. 🙂
Cathy
October 8, 2008 at 7:57 am
Here is the updated script.
I have also updated the online script, Just waiting for it to be published.
/**
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:
08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/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
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
**/
USE MASTER
GO
BEGIN
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
CREATE TABLE #tuser (
DBName VARCHAR(50),
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
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,
NULL AS Default_db_Name,
NULL as default_Schema_name,
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
'
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb '
SELECT
''?'',
u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM ?.sys.database_principals u
LEFT JOIN (?.sys.database_role_members m
JOIN ?.sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN ?.sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R''
/*and u.name like ''tester''*/
order by u.name
'
SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName
DROP TABLE #TUser
END
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[/url]
October 8, 2008 at 11:52 am
CAGreensfelder (10/2/2008)
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_namenot in SQL2K)
????????(default_schema_name????not in SQL2K)
????????(principal_idnot in SQL2K)
uidis 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
After I run the script the UserName and the GroupName are the same.
Shouldn't the GroupName reflect which group the UserName is associated with....if username: xyz has dbo rights to a database shouldn't dbo show in the GroupName.
Please advise.
Thanks
October 8, 2008 at 11:55 am
shiva challa (10/8/2008)
Here is the updated script.I have also updated the online script, Just waiting for it to be published.
/**
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:
08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/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
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
**/
USE MASTER
GO
BEGIN
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
CREATE TABLE #tuser (
DBName VARCHAR(50),
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
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,
NULL AS Default_db_Name,
NULL as default_Schema_name,
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
'
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb '
SELECT
''?'',
u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM ?.sys.database_principals u
LEFT JOIN (?.sys.database_role_members m
JOIN ?.sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN ?.sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R''
/*and u.name like ''tester''*/
order by u.name
'
SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName
DROP TABLE #TUser
END
Getting this error when I run this script in sql 2000:
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'Tempdb.sys.objects'.
Please advise.
Thanks
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply