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
adam-368690
adam-368690
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 32
Nice... works great.

Thanks,
-Adam
FreeHansje
FreeHansje
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1251 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
shivaram challa
shivaram challa
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 341
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
Attachments
jo.wright
jo.wright
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
Attachments
Users.sqlplan (28 views, 3.00 KB)
Andeavour
Andeavour
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: 1068 Visits: 932
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





CleverSQLUserID
CleverSQLUserID
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 282
--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
aleksey donskoy
aleksey donskoy
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 519
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
david.shink
david.shink
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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
shivaram challa
shivaram challa
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 341
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
wpgrady
wpgrady
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
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