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)document.forms.submit() Which works, however if form 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
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