SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Inactive logins on sql server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlpal2007
Posting Yak Master

184 Posts

Posted - 10/24/2007 :  08:54:21  Show Profile  Reply with Quote
Hi All,

I have to come up with the script which can show all the sql accounts which are inactive (the accoutns are there in syslogins but not in sysusers table on each database)

The active sql accounts and their permissions

something like below format:
login mapped user database permissions

Can anyone tell me how to do it?

Thanks,
-P

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 10/24/2007 :  10:39:57  Show Profile  Visit spirit1's Homepage  Reply with Quote

USE yourDB
SELECT 	*
FROM	sysusers U
        join master..syslogins L on U.sid = L.sid



_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Edited by - spirit1 on 10/24/2007 10:40:08
Go to Top of Page

sqlpal2007
Posting Yak Master

184 Posts

Posted - 10/24/2007 :  10:48:58  Show Profile  Reply with Quote
I result set is different when I do the join with syslogins. If I run the query
select * from currentDB..sysusers .......the result is more than if I do the join.

Here is what I am doing right now. I am refering to only sysusers. But now the problem is what if the user is dropped from the sysusers but is there in syslogins. I am missing those users.

How would I modify this query so that I get all the users from sysusers and the logins?

create table #dbusers
(
sid varbinary(85),
uname varchar(100)
)

create table #allusers
(
sid varbinary(85),
uname varchar(100),
lname varchar(100)
)

declare @dbname varchar(100),
@sql varchar(1000)

declare database_cursor cursor
for select name from master..sysdatabases
order by name
for read only

open database_cursor
fetch next from database_cursor into @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
select @sql = 'INSERT INTO #dbusers ' +
'select u.sid, u.name from ' + @dbname + '..sysusers u ' +
'where altuid <> 1'
exec (@sql)

INSERT INTO #allusers (sid, uname)
SELECT sid, uname FROM #dbusers
WHERE NOT EXISTS
(SELECT uname FROM #allusers
WHERE uname = #dbusers.uname)

DELETE FROM #dbusers
FETCH NEXT FROM database_cursor INTO @dbname

end

close database_cursor
deallocate database_cursor

select * from #allusers order by uname
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 10/24/2007 :  23:06:14  Show Profile  Reply with Quote
Or just run sp_helplogins.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 10/25/2007 :  03:46:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
i always forget about that one

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Edited by - spirit1 on 10/25/2007 03:46:29
Go to Top of Page

Kitkens
Starting Member

2 Posts

Posted - 06/18/2014 :  13:31:43  Show Profile  Reply with Quote
How does sp_helplogins show an inactive login? I also, need to find the inactive logins and users on the systems to remove the logins and users that are no longer active. Is there anyway to find this information without seting up auditing?

Linda
Go to Top of Page

jeanmarc22
Starting Member

Canada
1 Posts

Posted - 07/10/2014 :  21:47:22  Show Profile  Reply with Quote
I have the same problem. I found part of solution found in
http://www.mssqltips.com/sqlservertip/2088/sql-server-security-auditing-to-match-logins-and-users/
by K. Brian Kelley

with some modification (I created a SP for it):
DECLARE @TSQL NVARCHAR(4000);

SET @TSQL = 'SELECT Database_Name, l.name AS ''login'', d.name
COLLATE DATABASE_DEFAULT as ''user'' FROM master..syslogins l LEFT OUTER JOIN (
';
-- SQL2005+ master..syslogins become master.sys.server_principals

SELECT @TSQL = @TSQL + 'SELECT ''' + name + ''' AS Database_Name, sid, name
FROM [' + name + ']..sysusers
UNION ALL '
FROM master..sysdatabases WHERE name NOT IN ('master', 'tempdb');
-- SQL2005+ master..sysdatabases become master.sys.database_principals
-- SQL2005+ [' + name + ']..sysusers become [' + name + '].sys.sysusers

SET @TSQL = LEFT(@TSQL, LEN(@TSQL) - 10) + ')
d ON l.sid = d.sid WHERE d.name COLLATE DATABASE_DEFAULT IS NULL;';
-- SQL2005+ WHERE l.type in (''S'',''U'',''G'') AND d.name COLLATE DATABASE_DEFAULT IS NULL;';

--PRINT @TSQL -- to test query
EXECUTE sp_executesql @TSQL;

-- and add this to check orphans logins
EXEC sp_validatelogins


-- tested in SQL2000 and SQL2008R2


Jean-Marc
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000