sachintha81
asked on
SQL Query syntax : IF NOT EXISTS?
First I creat a Login to SQL Server using
CREATE LOGIN NT AUTHORITY\NETWORK SERVICE FROM WINDOWS WITH DEFAULT_DATABASE="MyDB";
Here, NT AUTHORITY\NETWORK SERVICE is the name of the Login I created and MyDB is the DataBase I associate it with.
After creating it I need to change some settings of the created login. Following is the manual procedure I usually do.
1. Open SQL Server Management Studio
2. In the "Object Explorer", expand "Security" -> "Logins"
3. Right Click the newly created login "NT AUTHORITY\NETWORK SERVICE" -> Select "Properties"
4. In the "Select a page" section select "User Mapping"
5. In the right hand side, there is a section called "Users mapped to this login". There, under the "Map" section check the CheckBox relavent to MyDB, then under "Default Schema" click the button that appears.
6. In the appearing "Select Schema" windown, click "Browse", select MyDB from the new window that opens and click OK. Then press OK in the "Select Schema" window and return to the previous window. (Bear in mind that at the time of the creation of MyDB, I also create Schemas, so that I am able to select that from "Select Schema" window. In your PC, you may not be able to do so if you don't create a schema when you create a DB.)
7. There, in the part at the bottom which says "Database role membership for : MyDB", check "db_owner". ("public" is already checked, so leave it as it is)
8. Then click OK in the Login Properties and apply the above settings.
I need this process to be done using SQL Queries, so following is the query I use.
But not just that, I also want to check if the login and the users exist before they are created.
I manged to check for the login using the following:
IF EXISTS (select loginname from master.dbo.syslogins where name = N'NT AUTHORITY\NETWORK SERVICE' and dbname = 'MyDB')
That worked fine, but I don't know how I should look for the users if they exist or not.
Any ideas?
Help with code segments will be greatly appreciated.
CREATE LOGIN NT AUTHORITY\NETWORK SERVICE FROM WINDOWS WITH DEFAULT_DATABASE="MyDB";
Here, NT AUTHORITY\NETWORK SERVICE is the name of the Login I created and MyDB is the DataBase I associate it with.
After creating it I need to change some settings of the created login. Following is the manual procedure I usually do.
1. Open SQL Server Management Studio
2. In the "Object Explorer", expand "Security" -> "Logins"
3. Right Click the newly created login "NT AUTHORITY\NETWORK SERVICE" -> Select "Properties"
4. In the "Select a page" section select "User Mapping"
5. In the right hand side, there is a section called "Users mapped to this login". There, under the "Map" section check the CheckBox relavent to MyDB, then under "Default Schema" click the button that appears.
6. In the appearing "Select Schema" windown, click "Browse", select MyDB from the new window that opens and click OK. Then press OK in the "Select Schema" window and return to the previous window. (Bear in mind that at the time of the creation of MyDB, I also create Schemas, so that I am able to select that from "Select Schema" window. In your PC, you may not be able to do so if you don't create a schema when you create a DB.)
7. There, in the part at the bottom which says "Database role membership for : MyDB", check "db_owner". ("public" is already checked, so leave it as it is)
8. Then click OK in the Login Properties and apply the above settings.
I need this process to be done using SQL Queries, so following is the query I use.
But not just that, I also want to check if the login and the users exist before they are created.
I manged to check for the login using the following:
IF EXISTS (select loginname from master.dbo.syslogins where name = N'NT AUTHORITY\NETWORK SERVICE' and dbname = 'MyDB')
That worked fine, but I don't know how I should look for the users if they exist or not.
Any ideas?
Help with code segments will be greatly appreciated.
USE [MyDB]
GO
ALTER LOGIN [MyLogin] WITH DEFAULT_DATABASE=[MyDB]
GO
USE [MyDB]
GO
CREATE USER [MyLogin] FOR LOGIN [MyLogin]
GO
USE [MyDB]
GO
ALTER USER [MyLogin] WITH DEFAULT_SCHEMA=[mySchema]
GO
USE [MyDB]
GO
EXEC sp_addrolemember N'db_owner', N'MyLogin'
GO
Declare @userName varchar(50)
set @UserName='Ritesh123'
if exists(SELECT name FROM sysusers where name=@Username)
print 'exists'
else
print 'not exists'
if exists(select * from sys.syslogins WHERE name = 'sa') ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Author talked about logins, so I thought may be he just got confused login with user
ASKER
Actually I wanted to check for both logins and users, but had already figured out a way to check for logins as I've shown in my first post. Though it is a bit different from what Ritesh has shown.
Anyway, thank you for clearing both of them out. Excellent answer!
Anyway, thank you for clearing both of them out. Excellent answer!
user DataBaseName
SELECT name FROM sysusers