Forum Topic

Thanks for your participation and contribution.


Back to Security

How to Create SQL Server Logins for Active Directory Windows Logins?

Post a reply
1925 views
i90runner1
i90runner1
Create SQL Logins for Windows AD Accounts
 

CREATE LOGIN [MSFT\Domain Users] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

DECLARE @ADDomainUsers TABLE  ( 
   [AccountName] NVARCHAR(255) ,
   [Type] NVARCHAR(50) ,
   [Privilege] NVARCHAR(50) ,
   [LoginName] NVARCHAR(50) ,
   [PermissionPath] NVARCHAR(50) 
  )

INSERT INTO @ADDomainUsers
exec xp_logininfo 'MSFT\Domain Users' ,'members'

SELECT * FROM @ADDomainUsers

DROP LOGIN [MSFT\Domain Users]

USE [master] 
GO 
CREATE LOGIN [JohnDoe]  
WITH  
   PASSWORD=N'Pa$$word',  
   DEFAULT_DATABASE=[AdventureWorks],  
   CHECK_EXPIRATION=OFF,  
   CHECK_POLICY=OFF; 

USE [master] 
GO 
CREATE LOGIN [MSFT\JohnDoe] FROM WINDOWS  
WITH  DEFAULT_DATABASE=[AdventureWorks];

Enumerate User Permissions
EXECUTE AS LOGIN = 'MSFT\JohnDoe' ; EXECUTE AS USER ='MSFT\JohnDoe' ; --Verify the execution context is now login1. SELECT SUSER_NAME(), USER_NAME(); SELECT * FROM fn_my_permissions(NULL, 'SERVER'); SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); SELECT * FROM fn_my_permissions('AdventureWorks', 'DATABASE') ORDER BY subentity_name, permission_name ; --The following REVERT statements will reset the execution context to the previous context. REVERT;

Last updated

1-1 of 1

Reply to this discussion

You cannot edit posts or make replies: You should be logged in before you can post.