Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2008 R2 Connectivity / Permissions Issues

Posted on 2011-09-05
4
Medium Priority
?
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
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 2000 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:Richard Quadling
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

715 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