Solved

SQL 2008 R2 Connectivity / Permissions Issues

Posted on 2011-09-05
4
306 Views
Last Modified: 2012-05-12
I'm sure this will be a simple question for someone.  I have a new Sql 2008 R2 server with a database running on it.  My domain administrator account has rights to the databse.  I need to be able to create an ODBC connection from a workstation to the database.  When I log in as administrator i can connect fine.  When I log in as my domain admin account it will not let me connect.  How do I add domain users to the list of accounts than can access this database?

Thanks!
0
Comment
Question by:Mohonk
  • 2
4 Comments
 
LVL 40

Expert Comment

by:RQuadling
ID: 36484820
Within SQL Server Management Studio, you need to make sure you have windows authentication enabled for the server.

You can then add users/groups to the logins for the server.

You can then add those logins to the database.
0
 

Author Comment

by:Mohonk
ID: 36484886
Where in the management studio would i grant domain users permission?
0
 
LVL 6

Accepted Solution

by:
jorgedeoliveiraborges earned 500 total points
ID: 36485085
>> Where in the management studio would i grant domain users permission?  <<
start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio

How to: Create a SQL Server Login
http://msdn.microsoft.com/en-us/library/aa337562.aspx
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36485765
Windows only authentication :
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
GO

Open in new window


Windows and SQL authentication :
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

Open in new window


Create domain group user with public access to a database :
USE [master]
GO
CREATE LOGIN [RICHARD-TOSH\HomeUsers] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]
GO
USE [ReportServer]
GO
CREATE USER [RICHARD-TOSH\HomeUsers] FOR LOGIN [RICHARD-TOSH\HomeUsers]
GO

Open in new window


These scripts are generated by the MSSMS (I'm using SQL 2008 R2 Dev Ed - excellent value for money for a sole developer).
0

Featured Post

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)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

920 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

13 Experts available now in Live!

Get 1:1 Help Now