Inactive logins on sql server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Inactive logins on sql server

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-10-24 : 08:54:21

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

11752 Posts

Posted - 2007-10-24 : 10:39:57

[code]
USE yourDB
SELECT *
FROM sysusers U
join master..syslogins L on U.sid = L.sid
[/code]

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

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-10-24 : 10:48:58

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-24 : 23:06:14

Or just run sp_helplogins.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-25 : 03:46:19

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

Kitkens
Starting Member

2 Posts

Posted - 2014-06-18 : 13:31:43

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

jeanmarc22
Starting Member

1 Post

Posted - 2014-07-10 : 21:47:22

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources