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

List all Usernames, Roles for all the databases. Expand / Collapse
Author
Message
Posted Monday, March 23, 2009 2:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 24, 2011 2:19 AM
Points: 10, Visits: 120
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


  Post Attachments 
permission.txt (8 views, 2.04 KB)
Post #681263
Posted Monday, March 23, 2009 2:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 24, 2011 2:19 AM
Points: 10, Visits: 120
Finally i got the script running, but it gave me mssg

Msg 208, Level 16, State 1, Line 3
Invalid object name 'New_Oil_Export.dbo.sysUsers'.

(365 row(s) affected)

(365 row(s) affected)



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??????
Post #681266
Posted Monday, March 23, 2009 9:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:40 AM
Points: 145, Visits: 336
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
Post #681534
Posted Monday, March 23, 2009 11:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 24, 2011 2:19 AM
Points: 10, Visits: 120
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....

Post #682063
Posted Tuesday, March 24, 2009 4:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 24, 2011 2:19 AM
Points: 10, Visits: 120
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???
Post #682188
Posted Thursday, March 26, 2009 9:29 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:40 AM
Points: 145, Visits: 336
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
Post #684756
Posted Monday, April 06, 2009 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 04, 2010 11:15 AM
Points: 3, Visits: 32
Can someone post the modified script for 2008?
Post #691211
Posted Monday, April 06, 2009 10:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:40 AM
Points: 145, Visits: 336
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


  Post Attachments 
List all Usernames, Roles for all the databases_v3.TXT (28 views, 4.54 KB)
Post #691237
Posted Wednesday, April 15, 2009 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 04, 2010 11:15 AM
Points: 3, Visits: 32
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.

Post #697792
Posted Wednesday, April 15, 2009 12:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 15, 2009 3:25 PM
Points: 12, Visits: 9
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

Post #697808
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse