March 23, 2009 at 2:29 am
Hi shiva...
Your query looks really helpful...only if i could get thir running. Attached is the script i modified a little. I am getting error message
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 60
Incorrect syntax near '?'.
PLEASE HELP
March 23, 2009 at 2:40 am
Finally i got the script running, but it gave me mssg
[font="Arial"]Msg 208, Level 16, State 1, Line 3
Invalid object name 'New_Oil_Export.dbo.sysUsers'.
(365 row(s) affected)
(365 row(s) affected)[/font]
I RATHER ACCEPTED A GRID THAT DISPLAYED ROLES AND MEMBERS IN THEM. PLEASE EXPLAIN WHAT HAPPENED AS I AM WORRIED NOW....... DID IT MAKE ANY SERIOUS CHANGES TO MY DATABASES??????
March 23, 2009 at 9:18 am
Hi,
I can assure you that the script wouldn't make any changes.
Can you post the latest script you have, so that I can take a look at whats going on.
BTW, the script does just exactly that, Display a grid with the information.
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]
March 23, 2009 at 11:11 pm
Hi Shivaram!
Thanks for the response. Its put me to some rest as i was scared to death yesterday after running the script.
I code i excuted is more or less the same that is available in permission.txt file i had attached earlier. I did some light modification like removing spaces or so and it worked.
But i didnot see any grid as it didnot display one. It only gave me the message as sent to you earlier. Could it be because of executing the DROP #TUser table along with the script????
Please advice.....
Can i get a script / if you could modify your script such that i get something like this...?
Servername
........database name
..................database roles
.........................users
........database name
..................database roles
.........................users
Many thanks Shivam....
March 24, 2009 at 4:57 am
I HAVE ONE MORE QUERY SHIVARAM......
The sysdatabases gives a column sid which is system id of the database creator. How can we modify this such that we get the userid of the database creator?? Do you know this???
March 26, 2009 at 9:29 pm
Hi Rubinasd,
SID is a security identifier for the login, you can get the loginname from sys.syslogins table.
Name is the UserName in the corresponding database, for the corresponding login.
My take at arranging the script's output in the below fashion would be to create a "Pivot Table" in MS Excel spreadsheet and drop all the columns into "Row Area".
Servername
........database name
..................database roles
.........................users
........database name
..................database roles
.........................users
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]
April 6, 2009 at 10:17 am
Can someone post the modified script for 2008?
April 6, 2009 at 10:37 am
adam (4/6/2009)
Can someone post the modified script for 2008?
Hi Adam,
I made changes to the code so that it would work on 2008 as well. Post back if you see any issues.
I have attached the updated script.
updated the if statement to include version 10.xx
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]
April 15, 2009 at 11:58 am
Thanks!
Could someone post a screenshot of the expected output of the script would be?
I have run the script on 2008, no errors, but there is no output. The server has several databases with a couple of users. So not sure what the issue could be.
April 15, 2009 at 12:17 pm
adam (4/15/2009)
Thanks!Could someone post a screenshot of the expected output of the script would be?
I have run the script on 2008, no errors, but there is no output. The server has several databases with a couple of users. So not sure what the issue could be.
Hi Adam, I dont have access to a 2008 instance to test this code on. but I found the bug that was not letting the result show up. Also, Understand that this code doesn't make any changes, it just pulls existing information. The resultset was not showing up cause, the below IF statement was not TRUE for 2008(version 10.xx). The resultset will be a plain table in all the versions.
If you look in the code, there are 2 place where I check for the Instance's product version number.
I've updated them correctly, so that it works for 2000,2005 and 2008 as well.
Please let me know if you have any questions.
I changed the IF statement to the following:
IF (LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)), 1) = '9'
OR LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(2)), 2) = '10')
IF EXISTS (SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
April 15, 2009 at 3:23 pm
Nice... works great.
Thanks,
-Adam
July 8, 2009 at 8:41 am
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
July 9, 2009 at 8:20 am
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[/url]
January 28, 2010 at 6:46 am
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
October 28, 2010 at 5:53 am
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
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply