April 13, 2011 at 9:48 am
I've accepted the challenge of creating a DW from scratch (design, code, load, maintain, DBA, server security) for a small company. Simply put I'm doing everything & enjoying it even though I've never been a DBA before.
Using Microsoft SQL Server 2008 R2(RTM) - 10.50.1600.1 (x64)
Enterprise Edition (64 bit) on Windows NT 6.1
MSSMS, SSIS, SSAS, & Reporting Services are all on the same server as well as the Server Instance - small company, small DW, can't afford multiple licenses.
I've created pieces of the DW (boss needed results to show his bosses) otherwise I would have worked on security/backups/DBA stuff first.
Besides the 'sa' login the instance was created with 2 other logins that have public & sysadmin rights that use Window Authentication only to login.
One of those logins is my windows ID (domain/Userme), the other is THE Server Domain ID (domain/Mainuser).
Been using the book, Beginning SQL Server 2008 Administration, as a guide.
I want to get ALL my Database owners, SQL Server Agent jobs, proxy accounts, SSIS, SSAS, & backup jobs all under ONE ID. Currently my TESTdb is owned by Userme. Prdodb, Importdb, Report Server db, Report Server Temp db, SSIS, SSAS, SQL Server Agent jobs & Proxy ID are owned by Mainuser.
#1) Should I put ownership to 'sa' or create a DBAUser login & give sysadmin rights?
#2) When changing ownership what 'problems' should I be on the lookout for?
#3) What problems would I incur when changing the password to the main owner? Is there a script or sp I could run to make sure the password change gets implemented in the SSIS, SSAS & SQL Server Agent jobs?
Thanks in advance for any & all advice!
April 13, 2011 at 11:20 am
You should try to use an AD account at all cost.
I find it more secure because it's password policies are managed by AD.
Try not to use "sa" on any connection strings, because you cannot lower "sa" permissions.
Try to create an AD account and then grant the ownership to that account on the specified objects.
Before you do that, try to run a trace or database specific audit so you have an idea of what t-sql is being executed, that way you have an idea of the level of permissions needed.
Unless it is extremely necesary, do not grant sysadmin to the account.
In a BI environment, the accounts normally just need access to create objects, insert, delete and updates, but they do not need sysadmin permissions.
In a proxy account, just make sure that it owns the job and it will be able to run it at any time.
Trace what accounts are being used to connect to your sql server.
if you have auditing turned on, this procedure should give you an good idea as to who is connecting.
xp_readerrorlog
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 13, 2011 at 1:26 pm
Thanks for your reply.
I went looking to see if an AD account already existed, none found. I then located the server installation logs. When asked if the current user (network user ID) was to be used as the Admin Account, they answered 'FALSE'. I also saw this message:'Provision current user as Database Engine System Administrator: addcurrentuserassqladmin = "False". So I currently do not have an AD account.
But they did setup the following:
adssysadminaccounts = "domain\Mainuser" "domain\Userme"
sqlsysadminaccounts = "domain\Mainuser" "domain\Userme"
So, I need to hit the Admin book & BOL to see how to correctly create/setup an 'Admin Account'.
Question: I see in the Admin book (title in previous post) that they use the Server Name\Administrator as the AD account & they use Windows Authentication to login. Is this the preferred naming/authenticating way? Or can I just create an account -> SQLAdministrator & use SQL Server Authentication?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply