Logins or Users without any permission

Question

Logins or Users without any permission

balasach82

SSCrazy Eights

Points: 9653
More actions
February 13, 2015 at 1:05 am

#297660

How to find thelist of logins/users who do not have any permission (except default Public) access.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

aprathour.89 Default port Points: 1495 More actions · Answer 1

select sid,name from syslogins where name not like '##%' and name not like 'NT%' and

sysadmin=0 and securityadmin=0 and setupadmin=0 and serveradmin=0 and processadmin=0 and diskadmin=0 and bulkadmin =0 and dbcreator=0

The above script lists the logins with only public role.

-----------------
Aditya Rathour
SQL DBA

Not Everything that is faced can be changed,
but nothing can be changed until it is faced.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 2

Ok, My question is kind of misguiding.

I want to list the logins which does not have any permission to any database (in User_mapping tab).

Ex login: test

At one time "test" db_datareader and after sometime, it is removed. So, now the "test" login does not have any permission for any db. If i can list such logins, then those can be removed, because it is not used login/id.

aprathour.89 Default port Points: 1495 More actions · Answer 3

SET NOCOUNT ON

CREATE TABLE #temp(SERVER_name SYSNAME NULL ,Database_name SYSNAME NULL ,userName SYSNAME ,GroupName SYSNAME ,LoginName SYSNAME NULL ,DefDBName SYSNAME NULL ,DefSchemaName SYSNAME NULL ,UserID INT ,[SID] VARBINARY(85) )

DECLARE @command VARCHAR(MAX)

DECLARE @databases TABLE(Database_name VARCHAR(128) , Database_size INT , remarks VARCHAR(255))

INSERT INTO @databases EXEC sp_databases

SELECT @command = COALESCE(@command, '') + '

USE ' + database_name + '

insert into #temp (UserName,GroupName, LoginName,

DefDBName, DefSchemaName,UserID,[SID])

Execute sp_helpuser

UPDATE #TEMP SET database_name=DB_NAME(),

server_name=@@ServerName

where database_name is null

'

FROM @databases

print @command

EXECUTE ( @command )

select name from sys.sql_logins where name not in (select loginname from #temp where loginname is not null) and name not like '##%' and name not like 'NT%'

drop table #temp

Please check this , i think this will solve your problem

-----------------
Aditya Rathour
SQL DBA

Not Everything that is faced can be changed,
but nothing can be changed until it is faced.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 4

Awesome. Query worked.

Solutions is very easy to understand and i an wondering why i couldnt think of this.