| Author |
Topic |
|
honey_191
Starting Member
49 Posts |
 Posted - 2008-05-06 : 23:32:25
|
| Hi,can anybody tell me how to identify all the databases from all the servers that are being accessed by a particular group |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-06 : 23:36:14
|
| Only from SQL profiler. |
 |
|
|
honey_191
Starting Member
49 Posts |
Posted - 2008-05-07 : 00:02:45
|
| no,i mean those databases that a particular group has permissions to acess.for example,i need to find out all the databases that can be access by mainframe. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-07 : 00:19:53
|
quote:
Originally posted by sodeep
sp_helplogins 'Loginname'
|
|
|
|
honey_191
Starting Member
49 Posts |
Posted - 2008-05-07 : 00:58:40
|
| Do i need to run each and every login on every server? |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-05-07 : 10:28:37
|
| To identify all databases that a particular Windows group has access to, you would have to run through the following checklist:1) Is the group in the Windows local admin group of the server and BUILTIN\ADMINISTRATORS is still in the sysadmin server role?2) Does the group have access to the server? (i.e. it is in sys.server_principals)3) Is the group a member of the sysadmin server role on the instance?4) If yes, then does the group show up in any of the databases (sys.database_principals in each database)So, technically, no you will not have to go through each and every database, but you will likely go through a lot of them. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-07 : 10:34:38
|
| Didn't get your question? You are saying Particular group and each and every logins. Post your question correctly.What is the requirement? |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-05-07 : 11:12:17
|
| To be fair, is the question "which databases can be accessed?" or "what databases are they actually using?" The second question can only be answered by Profiler, but gets highly muddled when you introduce web applications that can proxy users under a different login such as an application pool in IIS. In general, it is almost more effective to go to the group, and ask them what they are using. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-07 : 15:33:21
|
| Sp_helplogins lists all sql logins and which db they have permission. |
|
|
|
honey_191
Starting Member
49 Posts |
Posted - 2008-05-08 : 15:06:10
|
| If i want to find out on all the servers then?is there any query to find out on all the servers? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-08 : 15:11:09
|
| NO. What does sp_helplogins return ? Are you talking about particular group or each and every logins? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-08 : 15:30:49
|
| You can query sp_helplogins on each of your servers if you were to run sqlcmd with sp_helplogins once per server. If you've got a list of your servers somewhere, it'd be fairly easy to write out each sqlcmd command.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
|
Identifying Databases