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 «««34567»»»

List all Usernames, Roles for all the databases. Expand / Collapse
Author
Message
Posted Wednesday, April 15, 2009 3:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 4, 2010 11:15 AM
Points: 3, Visits: 32
Nice... works great.

Thanks,
-Adam
Post #697975
Posted Wednesday, July 8, 2009 8:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 6, 2012 8:30 AM
Points: 879, Visits: 810
It might be a great script, but I still have the errors mentioned in the first posting; I thought it was rectified. I cannot find an easy way to remove these 'illegal' characters, hence I cannot test it. Pity


Greetz,
Hans Brouwer
Post #749324
Posted Thursday, July 9, 2009 8:20 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
FreeHansje (7/8/2009)
It might be a great script, but I still have the errors mentioned in the first posting; I thought it was rectified. I cannot find an easy way to remove these 'illegal' characters, hence I cannot test it. Pity


Please find the attached file and you should be able to run it.
Issue is, when you copy code from the webpage, it brings in all the preceeding spaces as misc' charecters.
Thanks,
Shiva


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 
list all Usernames, Roles for all the databases.txt (2,202 views, 3.65 KB)
Post #750274
Posted Thursday, January 28, 2010 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 16, 2010 3:40 AM
Points: 12, Visits: 14
Hiya,

I've been playing around with this today as I have SQL2000 and I have made it work using the following attached code

Kind regards

Jo Wright




  Post Attachments 
Users.sqlplan (15 views, 3.66 KB)
Post #855164
Posted Thursday, October 28, 2010 5:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:44 AM
Points: 999, Visits: 869
Had a play around as well, and this runs on a Central Management Server across multiple SQL boxes with multiple builds (2000, 2005 & 2008).

USE MASTER
GO

BEGIN

IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8'
begin
IF EXISTS (SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%')
begin
DROP TABLE #TUser
end
end
ELSE
begin
IF EXISTS (SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%')
begin
DROP TABLE #TUser
end
end

CREATE TABLE #Tuser (
DBName SYSNAME,
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name SYSNAME NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT)

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
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
JOIN [?].dbo.sysUsers r
ON m.groupuid = 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 not in (''public'',''dbo'',''guest'',''sys'')
ORDER BY u.name
'
ELSE
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
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 not in (''public'',''dbo'',''guest'',''sys'')
order by u.name
'

SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName

DROP TABLE #TUser
END




Post #1012230
Posted Monday, January 31, 2011 8:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:26 PM
Points: 8, Visits: 277
--minor changes so it works on a case-sensitive server
USE master
GO

BEGIN

IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8'
begin
IF EXISTS (SELECT TOP 1 * FROM tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%')
begin
DROP TABLE #TUser
end
end
ELSE
begin
IF EXISTS (SELECT TOP 1 * FROM tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%')
begin
DROP TABLE #TUser
end
end

CREATE TABLE #TUser (
DBName SYSNAME,
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name SYSNAME NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT)

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
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
JOIN [?].dbo.sysUsers r
ON m.groupuid = 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 not in (''public'',''dbo'',''guest'',''sys'')
ORDER BY u.name
'
ELSE
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
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 not in (''public'',''dbo'',''guest'',''sys'')
order by u.name
'

SELECT *
FROM #TUser
ORDER BY DBName,
[Name],
GroupName

DROP TABLE #TUser
END
Post #1056239
Posted Monday, December 31, 2012 11:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:35 AM
Points: 20, Visits: 359
Great script.
2 things.
1. Proper version handling.
The version expresssion should be handled as this:

CAST((LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(2)),
2)) AS INT)

Then it will work for SQL 2008 and up

2. Database names should be bracketed "[" and "]" to handle spaces and dashes in the database names.

But the idea is awesome. It gives plenty of login/user/role info that is difficult to collect othewise.
Good idea for a report too.
Thanks
Alex Donskoy
SQL Server DBA Greenberg & Trauriq PA, Miami FL
Post #1401460
Posted Friday, July 12, 2013 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 12:05 PM
Points: 9, Visits: 241
Shiva:
Thank you so much. I am just now seeing your post of 12/31/12. I have had the experience of encountering a SQL Server instance with lots of databases and lots of logins, and needing to know all of the databases each login can connect to. This script will help a lot. You have done many of us out there a big favor. Keep up the good work.

David Shink
Post #1473189
Posted Tuesday, July 16, 2013 9:38 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
David, Thank you for the kind words!!


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 #1474193
Posted Tuesday, October 29, 2013 8:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 20, 2014 4:56 PM
Points: 2, Visits: 7
I get the following error when running SQL 2008 R2

Msg 173, Level 15, State 1, Line 18
The definition for column 'ServerNamevarchar' must include a data type.


Gratefully
Will
Post #1509376
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse