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.
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 permissionssomething like below format:login mapped user database permissionsCan anyone tell me how to do it?Thanks,-P |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-24 : 10:39:57
|
[code]USE yourDBSELECT *FROM sysusers U join master..syslogins L on U.sid = L.sid[/code]_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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 queryselect * 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 cursorfor select name from master..sysdatabasesorder by namefor read onlyopen 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 endclose database_cursordeallocate database_cursorselect * 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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS 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 inhttp://www.mssqltips.com/sqlservertip/2088/sql-server-security-auditing-to-match-logins-and-users/by K. Brian Kelleywith some modification (I created a SP for it):DECLARE @TSQL NVARCHAR(4000);SET @TSQL = 'SELECT Database_Name, l.name AS ''login'', d.nameCOLLATE DATABASE_DEFAULT as ''user'' FROM master..syslogins l LEFT OUTER JOIN (';-- SQL2005+ master..syslogins become master.sys.server_principalsSELECT @TSQL = @TSQL + 'SELECT ''' + name + ''' AS Database_Name, sid, nameFROM [' + name + ']..sysusersUNION ALL 'FROM master..sysdatabases WHERE name NOT IN ('master', 'tempdb');-- SQL2005+ master..sysdatabases become master.sys.database_principals-- SQL2005+ [' + name + ']..sysusers become [' + name + '].sys.sysusersSET @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 queryEXECUTE sp_executesql @TSQL;-- and add this to check orphans loginsEXEC sp_validatelogins-- tested in SQL2000 and SQL2008R2Jean-Marc |
|
|
|
|
|
|
|