Answered by:
Set username and password after create

Question
-
Hello
i create a database in VS201 by SQL server Express now i want seta user name and password fro this database
1- how can i do it in VS2010
2- how can i do it in Sql server R2 2008 (if i connect to .\sqlserverExpress)
thanks
Hossein Aftabi http://www.internationalsell.comSunday, July 31, 2011 2:05 PM
Answers
-
It's not wholly clear what you are asking for. Are you asking for setting a password on a database like you can in Access? (And which is not very secure, as it can be cracked.) That is not possible in SQL Server.
In SQL Server 2008 authentication is always on server level. This means that the user is authenticated to the server. The user can then access a database if he has been granted access to that database.
There are two ways a user can be authenticated to SQL Server. One is Windows authentication. This means that you log on to SQL Server with your Windows login, without specifying your password. Not that you cannot log on to SQL Server with a different Windows login.
The other way is SQL Server authentication. This means that you provide a username and password stored inside SQL Server. Windows authentication is usually preferred, both for management and security reasons, but there are situations where Windows authentication is not possible.
Once you have been let into SQL Server, you never specify a password to access a certain database. However, you may have to specify a password to access encrypted data.
Now that you know a little more about users and logins in SQL Server, maybe you can think a little more of what you want to achieve.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seSunday, July 31, 2011 7:13 PM -
You are using Windows Authentication. You used a Windows user name and password (or possibly no passord) when you logged into Windows. So Windows knows who you are, meaning Windows has authenticated you. SQL Server is trusing Windows. So you do not need to provide user name and password. And since you installed SQL Server, SQL Server made you an administrator.
To test this, Log into Windows as different Windows user. If you try to connect to SQL Server, you will probably fail. Because you haven't told SQL Server to trust that second Windows user.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warrantyMonday, August 1, 2011 9:02 PM -
And there is no way to tell this Crystalviewer that you are using Windows authentication? (May also be called "trusted connection" or "integrated security".)
But if you want to use SQL authentication, you must first enable this on your instance, if you did do so at Setup. For this you need SQL Server Management Studio Express. Right-click the server in the Object Explorer, select Properties and then the Security tab. You need to restart SQL Server.
Once you have done this you can run this in your database:
CREATE LOGIN user1 WITH PASSWORD = 'TopP$ecrEEtT' CREAET USER user1
And then grant this user the same permissions that you granted the user you are using now.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seMonday, August 1, 2011 10:04 PM
All replies
-
It's not wholly clear what you are asking for. Are you asking for setting a password on a database like you can in Access? (And which is not very secure, as it can be cracked.) That is not possible in SQL Server.
In SQL Server 2008 authentication is always on server level. This means that the user is authenticated to the server. The user can then access a database if he has been granted access to that database.
There are two ways a user can be authenticated to SQL Server. One is Windows authentication. This means that you log on to SQL Server with your Windows login, without specifying your password. Not that you cannot log on to SQL Server with a different Windows login.
The other way is SQL Server authentication. This means that you provide a username and password stored inside SQL Server. Windows authentication is usually preferred, both for management and security reasons, but there are situations where Windows authentication is not possible.
Once you have been let into SQL Server, you never specify a password to access a certain database. However, you may have to specify a password to access encrypted data.
Now that you know a little more about users and logins in SQL Server, maybe you can think a little more of what you want to achieve.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seSunday, July 31, 2011 7:13 PM -
Hello and thanks for answer
i description about my problem: in first time i create a database with VS2010 With add new item .
when i create a sql express database it create with out user name and password
after promote my project for use crystalviewer it asked me about username and password for connect to database
however i don't set any username and password and i set Windows authentication for login to Database
i aked about this problem an sap forum and in MSDN Forums nout i don't get god answer
ok
in final i change my way and i think it's better that i set a username and password in my database
ok
Now i have a database without username and password (Windows authentication)
and i need set user name and password for mydatabase that i create it many time ago
Thanks
Hossein Aftabi http://www.internationalsell.comMonday, August 1, 2011 6:42 PM -
You are using Windows Authentication. You used a Windows user name and password (or possibly no passord) when you logged into Windows. So Windows knows who you are, meaning Windows has authenticated you. SQL Server is trusing Windows. So you do not need to provide user name and password. And since you installed SQL Server, SQL Server made you an administrator.
To test this, Log into Windows as different Windows user. If you try to connect to SQL Server, you will probably fail. Because you haven't told SQL Server to trust that second Windows user.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warrantyMonday, August 1, 2011 9:02 PM -
And there is no way to tell this Crystalviewer that you are using Windows authentication? (May also be called "trusted connection" or "integrated security".)
But if you want to use SQL authentication, you must first enable this on your instance, if you did do so at Setup. For this you need SQL Server Management Studio Express. Right-click the server in the Object Explorer, select Properties and then the Security tab. You need to restart SQL Server.
Once you have done this you can run this in your database:
CREATE LOGIN user1 WITH PASSWORD = 'TopP$ecrEEtT' CREAET USER user1
And then grant this user the same permissions that you granted the user you are using now.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seMonday, August 1, 2011 10:04 PM