Click here to monitor SSC
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
rubinasd
rubinasd
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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
Attachments
permission.txt (9 views, 2.00 KB)
rubinasd
rubinasd
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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??????
shivaram challa
shivaram challa
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 341
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
rubinasd
rubinasd
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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....
rubinasd
rubinasd
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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???
shivaram challa
shivaram challa
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 341
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
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
Can someone post the modified script for 2008?
shivaram challa
shivaram challa
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 341
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
Attachments
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
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.
shivster
shivster
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: 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


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