Sql-server – Disabled SA user is listed as logged in

permissionssasql server

I was looking for the last time users have been logged into the system and saw SA as being used. Strange is that SA is disabled by default and not used on our systems. How is that possible, what am I missing?

Here is the code used to list users:

SELECT login_name [Login] , MAX(login_time) AS [Last Login Time]
FROM sys.dm_exec_sessions
GROUP BY login_name;

And here is the SA listed as disabled and as logged in:

Thanks.

Best Answer

This is because some "system" session are showing "sa" as the username.

I think you can add a

where is_user_process = 1

in your query to exclude those system session and you should no longer see "sa" as a connected user.

Related Solutions

Sql-server – Why do async_network_io wait types occur

ASYNC_NETWORK_IO somehow indicates that the client application isn't processing results as fast as SQL Server feeds them. This could be caused by an issue with the client application or with the network connection between the server and the client application.

Please refer to a post by Thomas LaRock

The ASYNC_NETWORK_IO wait indicates that one of two scenarios are happening. The first scenario is that the session (i.e., SPID) is waiting for the client application to process the result set and send a signal back to SQL Server that it is ready to process more data. The second is that there may be a network performance issue.

or this post by Joe Sack

As you may already be aware, the ASYNC_NETWORK_IO (seen in SQL 2005) and NETWORKIO (seen in SQL 2000) wait types are associated with either a calling application that is not processing results quickly enough from SQL Server or is associated with a network performance issue.

Since you are using entity framework this post by Brent Ozar may be useful too

Looking at the wait stats for these queries, I saw there was a lot of ASYNC_NETWORK_IO — often 1000+ milliseconds. That didn’t make any sense either! How can a query with so little CPU time and so few reads take so long to complete? It’s not like the application was asking for millions of rows and couldn’t consume the results fast enough.

Postgresql – Cannot revoke default privileges from postgresql user

Edited to answer the question related to the \ddp command not the \dp command as @personne3000 pointed out in the comment below.

You probably want to use ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA kpi REVOKE EXECUTE ON FUNCTIONS FROM intranet2;

This is because postgres is the user that was granted the default privilege of execute on the functions in the schema kpi and is granting it to intranet2, as noted by the permissions of intranet2=X/postgres

I created a small example to illustrate what's going on.

pgsql@[local]:5432:pgsql:=# CREATE ROLE bob;
CREATE ROLE
Time: 0.526 ms
pgsql@[local]:5432:pgsql:=# CREATE SCHEMA we_like_bob;
CREATE SCHEMA
Time: 0.608 ms

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob GRANT EXECUTE ON FUNCTIONS TO pgsql;
ALTER DEFAULT PRIVILEGES
Time: 1.480 ms
pgsql@[local]:5432:pgsql:=# \ddp
             Default access privileges
 Owner |   Schema    |   Type   | Access privileges 
-------+-------------+----------+-------------------
 bob   | we_like_bob | function | pgsql=X/bob
(1 row)

User pgsql has the execute privilege, X, granted by bob.

Try to revoke the execute privilege for the role pgsql, as pgsql (a superuser).

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE pgsql IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.176 ms
pgsql@[local]:5432:pgsql:=# \ddp
             Default access privileges
 Owner |   Schema    |   Type   | Access privileges 
-------+-------------+----------+-------------------
 bob   | we_like_bob | function | pgsql=X/bob
(1 row)

Didn't work because the command is not removing the bob role's default ability to grant execute on functions to role pgsql, it is removing the pgsql role's ability to revoke execute on functions from pgsql.

If we change it to remove the privilege of execute from the bob role for the role pgsql, then it works.

pgsql@[local]:5432:pgsql:=# ALTER DEFAULT PRIVILEGES FOR ROLE bob IN SCHEMA we_like_bob REVOKE EXECUTE ON FUNCTIONS FROM pgsql;
ALTER DEFAULT PRIVILEGES
Time: 0.644 ms
pgsql@[local]:5432:pgsql:=# \ddp
         Default access privileges
 Owner | Schema | Type | Access privileges 
-------+--------+------+-------------------
(0 rows)

pgsql@[local]:5432:pgsql:=#

Best Answer

Related Solutions

Sql-server – Why do async_network_io wait types occur

Postgresql – Cannot revoke default privileges from postgresql user

Related Question