locked
a login behaving like sysadmin but not in sysadmin group RRS feed

  • Question

  • I have a login that is taking on the 'dbo' user in a database. I did select user_name() while logged in with this login and it returned 'dbo'. But this login is not mapped to that db. The only time that I know of when that happens is when a login is sysadmin. But this login is only has public server role. So what am I missing?

    This login happens to be the windows account running the various SQL Server services. And I am not sure what kind of security level this account has on the Windows domain. I see a bunch of logins on SQL Server that start with '##' and 'NT Authority' and 'NT Service' Can this login somehow belong to one of these that somehow behave like sysadmin?

    Wednesday, March 20, 2013 1:06 PM

Answers

  • I have a login that is taking on the 'dbo' user in a database. I did select user_name() while logged in with this login and it returned 'dbo'. But this login is not mapped to that db. The only time that I know of when that happens is when a login is sysadmin. But this login is only has public server role. So what am I missing?

    This login happens to be the windows account running the various SQL Server services. And I am not sure what kind of security level this account has on the Windows domain. I see a bunch of logins on SQL Server that start with '##' and 'NT Authority' and 'NT Service' Can this login somehow belong to one of these that somehow behave like sysadmin?

    Funny how I answer my own questions when I write my questions down in this forum. I guess something about writing out your thoughts makes you think more clearly.... Anyway, it looks like the domain account that is running the SQL Agent Service automatically gets mapped to NT SERVICE\SQLSERVERAGENT login, thereby getting sysadmin role.

    If I am not correct, please let me know.


    Wednesday, March 20, 2013 1:21 PM
  • Yes.  'NT SERVICE\SQLSERVERAGENT' is the Per Service SID for the SQL Agent service.  Regardless of the service startup account used, the agent service itself can use the 'NT SERVICE\SQLSERVERAGENT' identity. 

    This has the benefit that the knowing the SQL Agent service account and password does not enable you to connect as a sysadmin.  

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, March 20, 2013 1:39 PM

All replies

  • I have a login that is taking on the 'dbo' user in a database. I did select user_name() while logged in with this login and it returned 'dbo'. But this login is not mapped to that db. The only time that I know of when that happens is when a login is sysadmin. But this login is only has public server role. So what am I missing?

    This login happens to be the windows account running the various SQL Server services. And I am not sure what kind of security level this account has on the Windows domain. I see a bunch of logins on SQL Server that start with '##' and 'NT Authority' and 'NT Service' Can this login somehow belong to one of these that somehow behave like sysadmin?

    Funny how I answer my own questions when I write my questions down in this forum. I guess something about writing out your thoughts makes you think more clearly.... Anyway, it looks like the domain account that is running the SQL Agent Service automatically gets mapped to NT SERVICE\SQLSERVERAGENT login, thereby getting sysadmin role.

    If I am not correct, please let me know.


    Wednesday, March 20, 2013 1:21 PM
  • Yes.  'NT SERVICE\SQLSERVERAGENT' is the Per Service SID for the SQL Agent service.  Regardless of the service startup account used, the agent service itself can use the 'NT SERVICE\SQLSERVERAGENT' identity. 

    This has the benefit that the knowing the SQL Agent service account and password does not enable you to connect as a sysadmin.  

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, March 20, 2013 1:39 PM