How to find out if a user exists in a db using sp_

问题:

I'm trying to use sp_executesql but can't seem to get the syntax right to do so. Here is what I'm trying out:

DECLARE @userName1 varchar(20)
DECLARE @userexists bit

SET @userName1 = 'testUser'

EXEC sp_executesql N'select @userexists = name FROM sys.database_principals WHERE name = ' + @userName1, N'@userexists bit output', @userexists output

PRINT @userexists

How can this work? Thanks for your help.

回答1:

Try this

EXEC sp_executesql 
  N'select @userexists=1 FROM sys.database_principals WHERE name = @userName1',
  N'@userName1 varchar(20), @userexists bit output',
  @userName1,
  @userexists output

In this case you will get 1 or empty result.

So, you can try this :

 EXEC sp_executesql 
  N'SET @userexists = CASE WHEN EXISTS(SELECT 1
                                FROM  sys.database_principals
                                WHERE name = @userName1)
                       THEN 1 ELSE 0
                       END',
  N'@userName1 varchar(20), @userexists bit output',
  @userName1,
  @userexists output