Click here to Skip to main content
15,867,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
USE [master]
GO
CREATE LOGIN [shi] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'shi', @rolename = N'sysadmin'
GO
USE [SMS2]
GO
CREATE USER [shi] FOR LOGIN [shi]
GO
USE [SMS2]
GO
ALTER USER [shi] WITH DEFAULT_SCHEMA=[dbo]
GO





Getting Exception:
XML
Msg 15407, Level 16, State 1, Line 1
'shi' is not a valid Windows NT name. Give the complete name: <domain\username>.
Msg 15007, Level 16, State 1, Procedure sp_addsrvrolemember, Line 68
'shi' is not a valid login or you do not have permission.
Msg 15007, Level 16, State 1, Line 1
'shi' is not a valid login or you do not have permission.
Msg 15151, Level 16, State 1, Line 1
Cannot alter the user 'shi', because it does not exist or you do not have permission.



Posted
Updated 17-Jun-12 19:52pm
v2

You'll have to create it first as a user, and then set up the correct permissions for the user.

1. You'll have to ensure that your DB is configured with both User auth and SQL auth. If using the Management Studio: right-click on the Server, select "Security" ensure that server authentication is "SQL Server and Windows Authentication mode";

2. In Security-logins, right click and select "New Login";, select SQL Authentication, use the username and password you like.
SQL
USE [master]
GO
CREATE LOGIN [ test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[MY_DATABASE], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO 


3. On the DB you want, in security, users, select new User. Select a username, and attach the login name you've just created, and select the roles you want to apply to this user (i.e. db_datareader, db_datawriter):
SQL
USE [MY_DATABASE]
GO
CREATE USER [myDefaultUser] FOR LOGIN [test]
GO
USE [MY_DATABASE]
GO
EXEC sp_addrolemember N'db_datareader', N'myDefaultUser'
GO
USE [MY_DATABASE]
GO
EXEC sp_addrolemember N'db_datawriter', N'myDefaultUser'
GO


That is it. Now you can create your connection string using this password.
Ref:stackoverflow.com
 
Share this answer
 
Comments
UL UL ALBAB 18-Jun-12 2:19am    
Thank you very much brother. Its working now.
Prasad_Kulkarni 18-Jun-12 2:26am    
Glad it helps!
You're welcome..
Sandeep Mewara 18-Jun-12 2:40am    
5!
Prasad_Kulkarni 18-Jun-12 3:01am    
Thank you Sandeep!
Manas Bhardwaj 18-Jun-12 5:40am    
Good +5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900