Solved

SQL 2008 R2 Connectivity / Permissions Issues

Posted on 2011-09-05
4
305 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

743 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

15 Experts available now in Live!

Get 1:1 Help Now