Checking If a SQL Server Login Already Exists

Checking if a SQL Server login already exists

From here

If not Exists (select loginname from master.dbo.syslogins 
    where name = @loginName and dbname = 'PUBS')
Begin
    Select @SqlStatement = 'CREATE LOGIN ' + QUOTENAME(@loginName) + ' 
    FROM WINDOWS WITH DEFAULT_DATABASE=[PUBS], DEFAULT_LANGUAGE=[us_english]')

    EXEC sp_executesql @SqlStatement
End

How to check if an Azure Sql Server server principal (Login) already exists

The following script will create the login on the master database at the logical Azure SQL server level.

IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = 'simonel')
    CREATE LOGIN simonel WITH PASSWORD = 'BS#ah12!!@#' 
ELSE
    PRINT 'Already exist'

The following acript will create the user at the database level, if it does not exist already.

IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name='simonel')
    CREATE USER simonel FOR LOGIN Blah WITH DEFAULT_SCHEMA = dbo
ELSE
    PRINT 'Already exists'

Check if login already added in User Defined Database Role

It has to be IS_ROLEMEMBER(@RoleName, @AddUser) as it is checking for the database role instead of IS_SRVROLEMEMBER (@RoleName, @AddUser).

Check if a user already exists in a DB

How to check if the USER is already created in the database or not in SQL?

How about:

USE (your database you want to check the user's existence in)

SELECT * 
FROM sys.database_principals
WHERE name = '(your user name to check here)'

sys.server_principals shows you the logins defined on the server level - sys.database_principals shows you the principals (e.g. user accounts) on a database level.

How do I check if a certain User exists under a specific Login in SQL?

What about something like this?

IF NOT EXISTS (SELECT * from master.dbo.syslogins WHERE name = 'PROD\Secure_user'
           AND loginname = 'PROD\Secure_user')
BEGIN
    CREATE USER [PROD\Secure_user] FOR LOGIN [PROD\Secure_user]
END

If the user is a windows user (select name, type_desc from sys.database_principals), I think it is a requirement that the loginname and name from syslogins match. But someone more knowledgeable should confirm. Possible source: http://msdn.microsoft.com/en-us/library/ms176060.aspx

---

In response to the feedback below, this joins on sid.

DECLARE @name varchar(100)
SET @name = 'PROD\Secure_user'

IF (
    SELECT count(*) 
    FROM master.dbo.syslogins sl
        INNER JOIN sys.database_principals dp ON sl.sid=dp.sid AND sl.name=dp.name
    WHERE sl.name=@name AND sl.loginname = @name
    ) > 0
BEGIN
    CREATE USER @name FOR LOGIN @name
END

Error that SQL Login already exists

Never add the same item twice in SSDT projects. Where the same item exists in more than one project, create a master project for the server.

Leave the users alone, just move the logins to a common project.

Think inheritance when designing SSDT projects. Do not forget to include the IncludeCompositeObjects switch for deployments of each individual database.

---

Related Topics