| Author |
Topic |
|
swekik
Posting Yak Master
126 Posts |
 Posted - 2008-05-15 : 17:33:56
|
| Hi everyone,I am new to this forum.I hope some body will help me in this,I am trying this for the past 6hrs.on my server there are some hundreds of sql logins are there,I need to identify the logins which doesn't associated with either any databases or dbroles,server roles.Later i need to delete those logins.Can any body help me in this.Thanks in advance. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-15 : 17:38:30
|
| "I am trying this for the past 6hrs"Didn't you search it?Run sp_helplogins and you will see all logins sp_helpuser |
 |
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2008-05-15 : 17:51:09
|
| Hi sodeep,Thanks for the reply,sp_helplogins is giving the info reg the db roles only,what abt server roles??Thats the main reason iam seraching for the past 6hrs |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-15 : 17:58:23
|
| Check sys.syslogins and see if logins has access to server rolesIf it is '1' means they have that server role.Query like:select loginname, securityadmin,sysadmin ...... from sys.syslogins.If logins has 0 in all ,it means it doesn't have server role.Donot delete default logins that comes with SQL server. |
|
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2008-05-16 : 10:05:42
|
| How to delete all the logins that doesn't have any db roles,server roles |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-16 : 10:14:53
|
| So did you find logins with no db and server role? Right click and delete. |
|
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2008-05-16 : 10:24:14
|
| If i have 400 logins like that,Do i need to go to each login and delete? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-16 : 10:31:32
|
| You can run as batch in T-SQL like Drop login .. go Drop login ... |
|
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2008-05-16 : 11:16:06
|
| I came to know that there is one linked server is there.All these logins are the logins to that linked server.Can i delete those logins on this server(not the linked server)?If i delete those logins on this server, is there any effect on the linked server? |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-05-16 : 14:28:02
|
| Personally, it sounds like you're playing with fire. Lock a few of the accounts, don't delete them, and see if you get any complaints (I bet you will!). You need to do more analysis of your situation before you proceed with your plan. What type of SQL authentication, mixed or windows only? Are these SQL logins or windows accounts? What are these 400 logins doing? Who set it up and can they help?Terry |
|
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2008-05-18 : 17:40:56
|
| all are sql logins,they dont have any mappings with anyone of the databases.. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-18 : 21:24:27
|
| If you remove those logins, user has to use other login to connect to sql and access linked server. But why they can't access remote sql server directly? Since those logins don't have permission on local server at all as you said. |
|
|
|
swekik
Posting Yak Master
126 Posts |
Posted - 2008-05-21 : 15:10:03
|
| Is there any script using system tables to delete all those logins(not one by one)? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-21 : 15:27:01
|
| There is no system stored procedure to do it. You'll have to write one.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 |
|
|
|
|
Sql logins