Solved

SQL Server permissions

Posted on 2013-05-22
9
299 Views
Last Modified: 2013-06-06
ALthough not a SQL DBA myself, we have a SQL Server Healthcheck soon whereby I am told the assessor needs full access to the SQL Server Operating Systems and SQL Server itself. For the OS we can grant them temporary admin access, but for SQL Server, what would they need, do you need to grant them access to a database account per database, or would domain admin rights typically cover admin access to the SQL Servers as well?

Also how is likely they would access SQL Server, would they remote onto the Server? Or use tools locally on a designated PC?
0
Comment
Question by:pma111
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 125 total points
ID: 39186750
This may not help directly but you could try asking them what they want in terms of access and what, if any, tools they need.  I'm sure they'll let you know. If you make them an administrator on the domain and allow administrators using Windows authentication to access SQL Server, then they should be able to get access to what they need.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 125 total points
ID: 39186779
While being integrated with system's security, SQL security has its own access control framework.  Typically, audits are realized by mapping sysadmin credential on SQL Server to a specific active directory user or group.  To do that, you simply declare the active directory/system user or group as a login on SQL Server.   Once created, you simply need to assign sysadmin credential to the freshly created login.

Hope this helps.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 125 total points
ID: 39186787
For your vendor, you can provide a local windows login with administrator privileges temporarily and an SQL Login with sysadmin privileges since DB level healtcheck requires sysadmin privileges and disable both windows and sql login once check is completed..
0
 
LVL 3

Author Comment

by:pma111
ID: 39186931
Its a shame you cant consolodate it to just one domain account, I would have thought systems with windows authentication domain admins will by default have sysadmin privileges  in every instance?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 9

Assisted Solution

by:VirastaR
VirastaR earned 125 total points
ID: 39186980
Hi,

Check this out!
http://www.techrepublic.com/blog/datacenter/reviewing-sql-server-permissions/466

also please provide more info on the SQL environment like version 2005/2008/R2 etc.

that would help us to help you better :)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39187010
>> Its a shame you cant consolodate it to just one domain account, I would have thought systems with windows authentication domain admins will by default have sysadmin privileges  in every instance?

Its the way it worked with older version of SQL Server.
However starting from SQL Server 2005 onwards, windows admins will not have sysadmin rights at SQL Server level unless otherwise granted explicitly for Security reasons.
0
 
LVL 3

Author Comment

by:pma111
ID: 39187097
So when you remote onto the server with your domain admin credentials, fire up sql server management studio, are you then prompted for either a SQL or AD username/password?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39187367
>> are you then prompted for either a SQL or AD username/password?

You can simply select any one of them..
0
 
LVL 9

Expert Comment

by:VirastaR
ID: 39188594
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now