command or script to find the "SQL Server Login(s)" that which dont have access to any databases on the server

Question

Hi All,

I m looking for command or script to find the "SQL Server Login(s)" that which dont have access to any databases on the server.

Thanks,

Satish Kumar.

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 1

Tricky. The database access is tracked in the database. See SELECT * FROM sys.database_principals; And also check sys.database_permissions for the CONNECT permission on the database. But a login can also have access by being a member of some of the fixed server roles, such as sysadmin. And a login for domain\sue could have access to a database because a domain group domain\managers has a login and is a database user.

It might be easier to use HAS_DBACCESS check every login against every database. But that could get tedious, too. It kind of depends on how many databases you have vs. how many logins you have.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 2

Yes.. thats the reason I m asking for any one have script with them...

If any one have plese update here....

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 3

Based on Rick's suggestion, here is a script:

DECLARE @user sysname

DECLARE @uselessusers TABLE (name sysname NULL)

DECLARE cur CURSOR STATIC LOCAL FOR
   SELECT name FROM sys.server_principals
   WHERE  type IN ('S', 'U')

OPEN cur

WHILE 1 = 1
BEGIN
   FETCH cur INTO @user
   IF @@fetch_status <> 0
      BREAK

   BEGIN TRY
      EXECUTE AS LOGIN = @user
   END TRY
   BEGIN CATCH
      PRINT 'Skipping user ' + @user + ': ' + error_message()
      CONTINUE
   END CATCH

   IF NOT EXISTS (SELECT *
                  FROM   sys.databases
                  WHERE  database_id >= 5
                    AND  has_dbaccess(name) = 1)
      INSERT @uselessusers(name) VALUES (@user)

   REVERT
END

CLOSE cur

SELECT name FROM @uselessusers

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Thanks Erland,

Just now i seen ur script. Even the the SQL login has NO access to any databases on the server, but it has server access. Hence i think it wil not work "EXECUTE AS LOGIN = @user"

(SELECT *
                  FROM   sys.databases
                  WHERE  database_id >= 5
                    AND  has_dbaccess(name) = 1

I think has_dbaccess will exists on sysusers table,

Please correct me if any thing wrong, bu the idea of your script is good but still not working up the mark.

for eg: create on SQL Server Login and dont map that newly created login to any database.

If we execute the script, then the output should be that newly created login which does not have access to any database.

Your help is highly appreciated.

Thanks.

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 5

Be sure that you run the script from the master or tempdb databases. If you run the script from any other database, it will not list any users, because EXECUTE AS will fail for users who do not have access to that databases.

Also pay attention to the Messages tab, where you will see a line for all users that for some reason cannot be impersonated.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Yes I have executed that script from Master database, after seeing your post i ran on one of the user database. its working.

Is their any chance to see output of SQL login names that which does not hat have access to any  databases @ single shot of execution.

Thanks,

Satish Kumar.

Answer 7

Is their any chance to see output of SQL login names that which does not hat have access to any  databases @ single shot of execution.

Not sure that I understand. The intention of the script was that it should exactly that. Can you clarify?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Hi Erland,

I m explaing little bit more clearly here:

for example:

1) Assume SQL Server has 3 Logins:

     1) ABC\James -- James Access to user database 'satish'

     2) ABC\Erland -- Erland has NO access to any databases (either system or msdb)

    3) SQLUSER -- It has access has some access to databases

I m looking for:

Once the execution completed, in the output i want to see the login "ABC\Erland" becuase it does not have access to any database on SQL Server

Thanks,

Satish Kumar.

Answer 9

And that is exactly what I believe that my script produces.

Note that by default all logins have access to master, msdb and tempdb through the guest user. You can revoke CONNECT to guest in msdb, but not master and tempdb. This is why I included the condition "database_id >= 5" in my query.

Also note that a Windows user may have access to a database through a group. If you believe that my script produces incorrect information, run this:

EXECUTE AS LOGIN = 'ABC/Erland'
go
SELECT name FROM sys.databases WHERE has_dbaccess(name) = 1
go
REVERT

---

Erland Sommarskog, SQL Server MVP, [e-mail]