asked
How can a user be active:show under Activity Monitor, but not be added under Security/Users or DatabaseA/Security/Users?
I view my SQL Server 2008 R2 Express Activity Monitor and I see CompanyNameA\UserNameA as a login using DatabaseA under processes. But I haven't added this user, I have added several users under the domain CompanyNameA but not her, and she doesn't show up under Security/Users or DatabaseA/Security/Users. How can this be? If she's an admin on the network, would that let her login even though I haven't explicitly granted her permission? I have several users created by SQL Server namely: BUILTIN\USERS granted roles db_datareader and db_datawriter on all my databases, guest on DataBaseA granted schema guest but no roles, NTAuthority\NetworkService on the instance granted sysadmin server role NTAuthority\System on the instance granted sysadmin server role This is a user that is OK, I just wondered how she is using the system without being granted access to the database.
Glad you got it sorted. You might also want to review those 2 service accounts(NTAuthority\NetworkService and NTAuthority\System) having sysadmin rights - that seems over generous. There are valid reasons for having these (see
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/27/question-is-nt-authority-system-a-sysadmin-in-your-sql-server-and-why.aspx) but should be the exception and not the norm.
We use VSS for a backup outside our building, so I do need NTAuthority\System. I do native backups also, but the backup files are located on the same server as our database. Someday, we will get a new server or migrate to the cloud, where our backups won't be on the same server as our database; I know this is advised. I guess I might need the other one, NTAuthority\NetworkService because of
https://msdn.microsoft.com/en-us/library/ff647402.aspx which says it is used for
ASP.NET application pools. This server is also our webserver. I know this is not desirable, but we are looking to get a new solution soon. And Wilfred van Dijk, I imagine the user is an Admin, and so belongs to BuiltIn\Users. I have no other groups specified on SQL Server.
ok my bad. Here is my answer at
https://msdn.microsoft.com/en-us/library/ms165636%28v=sql.105%29.aspx During setup of SQL Server Express a login is added for the BUILTIN\Users group. This allows all authenticated users of the computer to access the instance of SQL Server Express as a member of the public role. The BUILTIN\Users login can be safely removed to restrict Database Engine access to computer users who have individual logins or are members of other Windows groups with logins.
The user is in a group
16 People are following this question.
