I have a .net application which is connecting to the SQL Server using windows authentication.
We cannot use SQL Server authentication in the application. We have lot of Active Directory users there for our project. So we have to create separate login account for each Active Directory users in SQL Server rather than creating separate login account for each AD users, is there any way to use the active directory user group in SQL Server?
This question is related to
sql-server
sql-server-2008
security
authentication
You can use T-SQL:
use master
GO
CREATE LOGIN [NT AUTHORITY\LOCALSERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=yourDbName
GO
CREATE LOGIN [NT AUTHORITY\NETWORKSERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=yourDbName
I use this as a part of restore from production server to testing machine:
USE master
GO
ALTER DATABASE yourDbName SET OFFLINE WITH ROLLBACK IMMEDIATE
RESTORE DATABASE yourDbName FROM DISK = 'd:\DropBox\backup\myDB.bak'
ALTER DATABASE yourDbName SET ONLINE
GO
CREATE LOGIN [NT AUTHORITY\LOCALSERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=yourDbName
GO
CREATE LOGIN [NT AUTHORITY\NETWORKSERVICE] FROM WINDOWS WITH
DEFAULT_DATABASE=yourDbName
GO
You will need to use localized name of services in case of German or French Windows, see How to create a SQL Server login for a service account on a non-English Windows?
Go to the SQL Server Management Studio, navigate to Security, go to Logins and right click it. A Menu will come up with a button saying "New Login". There you will be able to add users and/or groups from Active Directory to your SQL Server "permissions". Hope this helps
Here is my observation. I created a login (readonly) for a group windows(AD) user account but, its acting defiantly in different SQL servers. In the SQl servers that users can not see the databases I added view definition checked and also gave database execute permeation to the master database for avoiding error 229. I do not have this issue if I create a login for a user.
Source: Stackoverflow.com