April 28, 2006 at 8:49 am
I inherited a SQL Server which uses the Windows 2003 BuiltIn Administrators Account and the Local Administrator Account on the SQL Server. The Local Administrator Account on the SQL Server is used to start the SQL Services. What I would like to do is create a Windows 2003 Group (prodadmin) and give it administrators access. Create a Windows 2003 User (produser) and assign it to the new Windows 2003 Group (prodadmin). Then, within SQL Server I would assign the Windows 2003 Group (prodadmin) to the Sysadmin Server Role, with access to the appropriate databases and assigned to the db_owner database role. Then I would delete the BuiltIn Administrators and the Local Administrator SQL Server Logins. The questions I have are; does the Windows 2003 User (produser) have to be set up as a SQL Server Login? What about as a user within a Database? Would I be able to start the SQL Server Services with the Windows 2003 User (produser) Login.
I also would like to use the produser SQL Server Login to execute SQL Jobs or as the owner of the SQL Jobs. Is this possible?
We are running SQL Server 2000 (sp3) on Windows 2003 EE.
Thanks in advance, Kevin
April 28, 2006 at 9:37 am
I'm not 100% sure I'm reading you right, but that looks like it should work fine.
If you use Enterprise Manager to reset the accounts which SQL Server uses for startup it should set the permissions accordingly. There's a list in BOL of exactly what permissions it needs to which directories and registry keys.
You won't have to set up produser as a SQL login. Because it's a member of prodadmin which you say you will make a sysadmin in SQL it should be fine. You don't need to assign it to db_owner on the database because as a sysadmin it can perform all functions in all databases anyway.
Be careful that you reassign any objects owned by the two logins you'll delete to be owned by something else before you try to delete the logins.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply