January 31, 2011 at 8:48 am
--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
December 31, 2012 at 11:42 am
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
July 12, 2013 at 11:58 am
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
July 16, 2013 at 9:38 am
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[/url]
October 29, 2013 at 8:21 am
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
October 29, 2013 at 9:06 am
Try this
ServerName varchar(256)
New code does works for SQL 2005 but it doesn't return any result for SQL 2008 or SQL 2008
R2
October 29, 2013 at 9:27 am
I ran this query on SQL 2008 R2 and received the following error.
Msg 173, Level 15, State 1, Line 18
The definition for column 'ServerNamevarchar' must include a data type.
thank u
Bill
October 29, 2013 at 9:30 am
leave a space between Servername and Varchar and script will run but it will not return any result for SQL 2008 or SQL 2008 R2
ServerName????????????varchar(256),
October 29, 2013 at 10:51 am
I have updated the script to get data for SQL Server 2008(tested) and SQL Server 2012(not tested). Once it gets published, you can get the script from the webpage. But until then, you can try the following to get results for 2008.
Replace this line (in two places):
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = ('9')
with this:
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(2)),2) in ('9','10','11')
And that should do the trick.
PS: I don't have have access to SQL Server 2012, if anyone can test it for 2012 and report back, I'd greatly appreciate 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]
October 29, 2013 at 11:45 am
shivaram challa (10/29/2013)
I have updated the script to get data for SQL Server 2008(tested) and SQL Server 2012(not tested). Once it gets published, you can get the script from the webpage. But until then, you can try the following to get results for 2008.Replace this line (in two places):
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = ('9')with this:
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(2)),2) in ('9','10','11')And that should do the trick.
PS: I don't have have access to SQL Server 2012, if anyone can test it for 2012 and report back, I'd greatly appreciate it.
I am not sure why the script goes through all of the machinations of checking SQL version, etc to drop a temp table. With this methodology as seen above a new version of the script is needed every time a new version of SQL comes out. I do not have a SQL 6.5 running, but I think this works all the way back to 6.5:
IF OBJECT_ID(N'tempdb..#TUser') IS NOT NULL DROP TABLE #TUser
Great script otherwise.
October 29, 2013 at 11:49 am
Hello, thank you for the script. I found that to make it work on SQL 2008 R2 I needed to change two lines to look for the value of '1' in addition to the value of '9':
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) IN ('9','1')
October 29, 2013 at 1:13 pm
Hi gshouse,
Actually, this script will work for 2000, 2005, 2008 and hopefully 2012. I dont know if this will work for 6.5 though.
The reason is sys tables that have this information has changed between 2000 and 2005 (and stayed the same since); like sys.database_principals vs dbo.sysUsers, etc.
You can safely modify the script to check if its 2000 and run its variant and run the other for everything else.
May be, I can create a second version of this script that will run against all new versions of SQL Server with out checking version number.
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 29, 2013 at 1:20 pm
Hi MMcPherson,
This will work more so becasue this statement will truncate the 10 and 11 to 1 (due to varchar(1)) and compare that against 1.
More accurate statement would be:
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(2)),2) in ('9','10','11')
On the bright side, keeping it your way will make it work for all the future versions till SQL Server version 19 🙂 even though its a bug. And in a way addressing gshouse's concern.
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 29, 2013 at 2:39 pm
shivaram challa (10/29/2013)
Hi gshouse,Actually, this script will work for 2000, 2005, 2008 and hopefully 2012. I dont know if this will work for 6.5 though.
The reason is sys tables that have this information has changed between 2000 and 2005 (and stayed the same since); like sys.database_principals vs dbo.sysUsers, etc.
You can safely modify the script to check if its 2000 and run its variant and run the other for everything else.
May be, I can create a second version of this script that will run against all new versions of SQL Server with out checking version number.
Right - not talking about the SQL 2005 paradigm shift. You do need the test for versions there. The test for versions to drop a temp table seems like overkill that is errorprone and inefficient. I'll fire up a 6.5 and check when I have a chance to be sure. But a simple one-liner to drop a table that should work SQL 6.5 through SQL 2014 and beyond is something I'd always rather have than 12 lines of code that needs to be updated with each new major version of SQL.
October 31, 2013 at 3:36 am
shivaram challa (10/29/2013)
Hi MMcPherson,This will work more so becasue this statement will truncate the 10 and 11 to 1 (due to varchar(1)) and compare that against 1.
More accurate statement would be:
IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(2)),2) in ('9','10','11')
Actually, going from varchar(1) to varchar(2) in the check for SQL Server 2005 throws a small bug:
I got empty resultsets until I changed '9' to '9.' ...
And this got me to another observation: There are differences between the two scripts given: (the direct download vs. the one shown in the blog).
The "direct download" has not been changed to use "varchar(2)" - so it still worked, which got me puzzled for a little while 😉
Viewing 15 posts - 46 through 60 (of 60 total)
You must be logged in to reply to this topic. Login to reply