March 12, 2009 at 10:15 am
Hi, I've read a few things within these forums re the above but none of them appear to be able to answer my question. After I install SQL on any server I'm quick to crack down on security by removing builtin\admins, changing sa name, removing ntauth\system etc.. So that I have total control over who can access SQL ie only I know the passwords of the service accounts that run the agent & SQL and no windows group has access in SQL.
However with the above Windows group and its Agent equivalent they are automatically given sysadmin rights to SQL. This means that anyone that adds a domain account to that group automatically gets FULL SQL sysadmin rights!! This means I don't have cnotrol as we have various peolpe that manage and are admins of the server that could add anyone (including themselves) into either of these groups then by default obtain full rights!!!
So my question is why do these 2 groups HAVE to be sysadmins. and what other access would I need to give my SQL & Agent service account to be able to run SQL if I removed sysadmin from this group. Bear in mind that I installed SQL with the SQL Service account (then removed it as sysadmin), so it should have all the rights it needs!!
thanks
March 12, 2009 at 11:49 am
This is the new security model for 2005. Instead of granting the Service account local admin what 2005 does is when you install it; it creates a local group for each service ...
So if you look at local groups on server you'll see
SQLServer2005ServiceName$ServerName$MSSQServer
Each of these groups then are assigned proper file system permissions and sql server permissions.
This was done to make it easier to change Service account and manage permissions. The MSSQLUser group contains your service account; the Service account should have Sysadmin with in SQL Server to access the system tables and catalog.
Hope that clears the idea a bit..
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 13, 2009 at 3:16 am
Hi thanks, but my question was whether I can remove it. I appreciate what its for but dont understand the point in removing accounts from sysadmin only to set up another group which allows full rights to a DB without the DBA knowing. At least with sysadmin you could remove builtin\admins so you had control. Can you remove these groups from SQL without effecting SQL, as long as your service accounts have appropriate permisssions...thanks
March 13, 2009 at 4:46 am
simon.letts (3/13/2009)
Hi thanks, but my question was whether I can remove it. I appreciate what its for but dont understand the point in removing accounts from sysadmin only to set up another group which allows full rights to a DB without the DBA knowing. At least with sysadmin you could remove builtin\admins so you had control. Can you remove these groups from SQL without effecting SQL, as long as your service accounts have appropriate permisssions...thanks
You can run all SQL services on one account(Account should be sysadmin and on windows level in a gr of administrators)
After doing this initially try running server by disabling those logins...
After some days of trial you can delete those logins but my personal opinion is keep those acc in disable state.
March 13, 2009 at 4:48 am
Nikhil (3/13/2009)
simon.letts (3/13/2009)
Hi thanks, but my question was whether I can remove it. I appreciate what its for but dont understand the point in removing accounts from sysadmin only to set up another group which allows full rights to a DB without the DBA knowing. At least with sysadmin you could remove builtin\admins so you had control. Can you remove these groups from SQL without effecting SQL, as long as your service accounts have appropriate permisssions...thanksYou can run all SQL services on one account(Account should be sysadmin and on windows level in a gr of administrators)
After doing this initially try running server by disabling those logins...
After some days of trial you can delete those logins but my personal opinion is keep those acc in disable state.
Do keep login on which services are running
March 13, 2009 at 9:06 am
simon.letts (3/13/2009)
Hi thanks, but my question was whether I can remove it. I appreciate what its for but dont understand the point in removing accounts from sysadmin only to set up another group which allows full rights to a DB without the DBA knowing. At least with sysadmin you could remove builtin\admins so you had control. Can you remove these groups from SQL without effecting SQL, as long as your service accounts have appropriate permisssions...thanks
I would not recommend it; you got to understand it is a local group only not domain group. The point of it is not to grant other users SYSADMIN but think about in extreem cases where there is service account for each service? Then these groups help you get organized in granting permissions.
I have not tried it; but if you really wanted you should be able to remove the group and add the user directly in. After adding the service account in Grant it sysadmin. But don't remove service account from that group on local computer because all file-level permissions are assigned to that group for SQL Server. Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply