Checking if a SQL Server login already exists

I need to check if a specific login already exists on the SQL Server, and if it doesn’t, then I need to add it.

I have found the following code to actually add the login to the database, but I want to wrap this in an IF statement (somehow) to check if the login exists first.

CREATE LOGIN [myUsername] WITH PASSWORD=N'myPassword', 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF 
GO

I understand that I need to interrogate a system database, but not sure where to start!

Visual Studio Database project: checking if SQL server login exists before creating it

When I create a Visual Studio database project for SQL 2012 and synchronise it with an existing database (using Compare Schema), I also synchronise a SQL server login. Visual Studio generates the foll

check if table exists sql server [duplicate]

Possible Duplicate: SQL Server: Check if table exists I’m working with Java and MS SQL Server 2008 and I want to create a table only after checking if it not already exists in the database. public

SQL Server Temp Table Already Exists? [duplicate]

This question already has an answer here: Drop temp table within IF ELSE statement 1 answer So this is weird, I am declaring a temp table in this bit of SQL, but I am only declaring it once bas

Checking if row already exists

I’m trying to make validation for a registration form to check whether a username/name + surname already exists. This is what I tried: Dim conn As New SqlConnection(Data Source=BRIAN-PC/SQLEXPRESS;In

Cannot Add a Sql Server Login

When I try to create a SQL Server Login by saying CREATE LOGIN [ourdomain/SQLAccessGroup] FROM WINDOWS; I get this error The server principal ‘ourdomain/SQLAccessGroup’ already exists. However, whe

C# SMO- checking if the table already exists at the destination server

I have written a C# code to transfer tables between two remote servers using SMO, what I want to know is that is there anyway to check if the table already exists in the destination server with the ex

Checking if form already exists

Is there a better command than this to check if a form is exists and if so submit it? Right now I’m using: if(document.forms[0])document.forms[0].submit() Which works, however if form[0] does not exi

Checking if Username already exists

I currently have this code that I wrote up: $username = $_POST[‘username’]; $password = $_POST[‘password’]; $rpassword = $_POST[‘rpassword’]; $usrip = $_SERVER[‘REMOTE_ADDR’]; $email = $_POST[’email’]

Checking if table exists or not in SQL Azure

Could anyone help me in checking whether table exists or not in sql azure?

how to check database already exists in sql server? and how stop the query if database already exists?

I have a query like this: IF EXISTS(select name from sys.databases where name = ‘AdventureWorks’) use AdventureWorks I want to use AdventureWorks if it already exists, but if it doesn’t exists, I wan

Answers

From here

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

EXEC sp_executesql @SqlStatement
End

This works on SQL Server 2000.

use master
select count(*) From sysxlogins WHERE NAME = 'myUsername'

on SQL 2005, change the 2nd line to

select count(*) From syslogins WHERE NAME = 'myUsername'

I’m not sure about SQL 2008, but I’m guessing that it will be the same as SQL 2005 and if not, this should give you an idea of where t start looking.

Try this (replace ‘user’ with the actual login name):

IF NOT EXISTS(
SELECT name 
FROM [master].[sys].[syslogins]
WHERE NAME = 'user')

BEGIN 
	--create login here
END

As a minor addition to this thread, in general you want to avoid using the views that begin with sys.sys* as Microsoft is only including them for backwards compatibility. For your code, you should probably use sys.server_principals. This is assuming you are using SQL 2005 or greater.

Here’s a way to do this in SQL Server 2005 and later without using the deprecated syslogins view:

IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'LoginName')
BEGIN
    CREATE LOGIN [LoginName] WITH PASSWORD = N'password'
END

The server_principals view is used instead of sql_logins because the latter doesn’t list Windows logins.

If you need to check for the existence of a user in a particular database before creating them, then you can do this:

USE your_db_name

IF NOT EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'Bob')
BEGIN
    CREATE USER [Bob] FOR LOGIN [Bob] 
END

what are you exactly want check for login or user ? a login is created on server level and a user is created at database level so a login is unique in server

also a user is created against a login, a user without login is an orphaned user and is not useful as u cant carry out sql server login without a login

maybe u need this

check for login

select 'X' from master.dbo.syslogins where loginname=<username>

the above query return ‘X’ if login exists else return null

then create a login

CREATE LOGIN <username> with PASSWORD=<password>

this creates a login in sql server .but it accepts only strong passwords

create a user in each database you want to for login as

CREATE USER <username> for login <username>

assign execute rights to user

 GRANT EXECUTE TO <username>

YOU MUST HAVE SYSADMIN permissions or say ‘sa’ for short

you can write a sql procedure for that on a database

create proc createuser
(
@username varchar(50),
@password varchar(50)
)
as
begin
if not exists(select 'X' from master.dbo.syslogins where [email protected])
begin
 if not exists(select 'X' from sysusers where [email protected])
 begin
exec('CREATE LOGIN '[email protected]+' WITH PASSWORD='''[email protected]+'''')
exec('CREATE USER '[email protected]+' FOR LOGIN '[email protected])
exec('GRANT EXECUTE TO '[email protected])
end
end
end

First you have to check login existence using syslogins view:

IF NOT EXISTS 
    (SELECT name  
     FROM master.sys.server_principals
     WHERE name = 'YourLoginName')
BEGIN
    CREATE LOGIN [YourLoginName] WITH PASSWORD = N'password'
END

Then you have to check your database existence:

USE your_dbname

IF NOT EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'your_dbname')
BEGIN
    CREATE USER [your_dbname] FOR LOGIN [YourLoginName] 
END