LIONS, TIGERS, & LOGINS, Oh What A Bear

Question

LIONS, TIGERS, & LOGINS, Oh What A Bear

YSLGuru

SSC-Insane

Points: 21661
More actions
July 13, 2010 at 11:52 am

#237675

Just curious if any of you have any tricks you use to manage a large number of Logins. For example when working in SSMS have you found any way to modify the way Logins are displayed? I have several hundred DB Users w/matching Logins for our primary accounting DB and its a real bear to deal with at times simply because of how many screens you have to scroll up & down when the DB Users or LOGINS node in SSMS is expanded.
I've wanted a way to edit SSMS view for some time especially the Activity Monitor but last I checked (about a year ago) there was no way to do this.
Any suggestions are most welcome.
Kindest Regards,
Just say No to Facebook!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Doug S. SSCrazy Points: 2801 More actions · Answer 1

You can write your own program using Visual Studio and the SQL Server management objects. Here's a link to the MSDN forums where you can learn more..

http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/threads"> http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/threads

Doug

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 2

A possible option is to make it a rule to never click on the "Logins" node in Object Explorer without right clicking it and selecting "Filter" first. However, you'll still be in trouble when managing permissions inside the database properties.

Other than that, I'd suggest getting really good with the T-SQL commands to manage logins. CREATE LOGIN, CREATE USER, sp_helprotect, etc.

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

Jim McLeod (7/13/2010)
A possible option is to make it a rule to never click on the "Logins" node in Object Explorer without right clicking it and selecting "Filter" first. However, you'll still be in trouble when managing permissions inside the database properties.
Other than that, I'd suggest getting really good with the T-SQL commands to manage logins. CREATE LOGIN, CREATE USER, sp_helprotect, etc.

Jim,

Thanks for the suggestion but unfrotunately the FILTER options avaiable in SSMS are incredibly limited. I have used them to find login created between a certain range which has been useful but over all not anything that a simple query of the logins table couldn't provide. I know I can get these using T-SQL I was just hoping for a nice GUI way using the existing SSMS product. Its amazing to me how with the 2008R2 release you still have no way to organize that thing for any of the nodes nor even a way to add to or edit the Activity Monitor report to include data as well as exlcude or filter.

Thanks

Kindest Regards,

Just say No to Facebook!

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Here are a two suggestions:

Why use individual users, how about using Active Directory groups ?

(This may take a bit of analysis and lunch for the AD/Windows admin)

Now the low-tech, but extremely obvious choice - abandon the GUI of SSMS for a query window !!!

(you'll be surprised just how easy the transact-SQL and SPs for login/user management really are)

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

YSLGuru SSC-Insane Points: 21661 More actions · Answer 5

rudy komacsar - Doctor "X" (7/27/2010)
Here are a two suggestions:
Why use individual users, how about using Active Directory groups ?
(This may take a bit of analysis and lunch for the AD/Windows admin)
Now the low-tech, but extremely obvious choice - abandon the GUI of SSMS for a query window !!!
(you'll be surprised just how easy the transact-SQL and SPs for login/user management really are)

Unfortunately the mission critical accounting software we use (our primary accounting system) does not work with NT/AD Users & Groups and so I have little choice in that area.

Clearly you can use the query window for anything and anyone with a basic understanding of SQL knows this. You however don't get the nice hierarchal view with it that the SSMS GUI gives you. Besides, if the GUI weren't very popular (and so most stuck with using the query tool for everything) then the GUI side of SSMS would not have evolved to the extent it has. Its just lacking in the area of filtering.

Thanks for taking time to chime in.

Kindest Regards,

Just say No to Facebook!

Doug S. SSCrazy Points: 2801 More actions · Answer 6

Here's a link to a Great MSDN article with code download for using SQL Server management objects to create your own GUI.

http://msdn.microsoft.com/en-us/magazine/cc163409.aspx"> http://msdn.microsoft.com/en-us/magazine/cc163409.aspx

Good luck

Doug

YSLGuru SSC-Insane Points: 21661 More actions · Answer 7

Doug S. (7/29/2010)
Here's a link to a Great MSDN article with code download for using SQL Server management objects to create your own GUI.
http://msdn.microsoft.com/en-us/magazine/cc163409.aspx"> http://msdn.microsoft.com/en-us/magazine/cc163409.aspx
Good luck

Sweet! This is what I was looking for. Now I just hope its applicable to the need I have. It seems form recent proejcts that every time we at my work try to do some custom develkopment on a Microosft platform (i.e. InfoPath on a SharePoint site) we get %85 -%90 there only to find ther'es %5-%10 that can't be done due tos ome bug or quirk.

Thank you Doug!

Kindest Regards,

Just say No to Facebook!

Doug S. SSCrazy Points: 2801 More actions · Answer 8

Let me know if you need some help with the coding..

Cheers

Doug