Following stored procedure can be used find out whether particular Windows user or group account is mapped to a current SQL instance . If given Windows user or group account exists in the SQL server it will returns the account name, type, privilege, mapped login name and permission path
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
USE [master]; GO CREATE PROCEDURE [dbo].[usp_Check_Windows_User] (@NTLogin NVARCHAR(128)) AS SET NOCOUNT ON DECLARE @UserList TABLE ( [AccountName] NVARCHAR(128), [Type] NVARCHAR(64), [Privilege] NVARCHAR(64), [Mapped Login Name] NVARCHAR(128), [Permission Path] NVARCHAR(MAX) ) INSERT INTO @UserList EXEC xp_logininfo IF EXISTS (SELECT * FROM @UserList WHERE [AccountName] = @NTLogin) ( SELECT * FROM @UserList WHERE [AccountName] = @NTLogin ) ELSE (SELECT 'User/Group dose not exists in the '+UPPER(@@SERVERNAME)+' Server instance. ' as Details) |
Leave a Comment