Andrew Chalk wrote:
> I have created a user in SQL Server Management Studio Express.
> However, whenever my ASP.NET application tries to connect I get the
> error Cannot open database "ABC" requested by the login. The login failed.
> Login failed for user 'Fred'.
>
> If I change my login string to Integrated Security=SSPI it works.
> Also, I was able to add a user successfully in SQL 2000 but
> SQLExpress is differnt.
> Can anyone tell me how to add a user and give him permissions for a
> particular database? Or refer me to a URL that does.
>
> Many thanks.

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
news:40acn2F19erhvU1@individual.net...
> hi Andrew,
> Andrew Chalk wrote:
>> I have created a user in SQL Server Management Studio Express.
>> However, whenever my ASP.NET application tries to connect I get the
>> error Cannot open database "ABC" requested by the login. The login
>> failed.
>> Login failed for user 'Fred'.
>>
>> If I change my login string to Integrated Security=SSPI it works.
>> Also, I was able to add a user successfully in SQL 2000 but
>> SQLExpress is differnt.
>> Can anyone tell me how to add a user and give him permissions for a
>> particular database? Or refer me to a URL that does.
>>
>> Many thanks.
>
> please verify your SQLExpress instance accepts standard SQL Server logins,
> enabling mixed security.. your connection string changed to ....Integrated
> Security=SSPI implies a trusted connection...
>
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
> made
> member of none, 1 or all of the fixed server roles, which include
> "sysadmin"
> role and so on...
>
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present....
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
>
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
>
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply
>

> database access, but a (database) user must be mapped to the corresponding
> login..

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
news:40acn2F19erhvU1@individual.net...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
>
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply
>

Andrew Chalk wrote:
> Andrea: Thanks you for your detailed reply.
>
> 1) I created a login under the server->security folder.
> I did not give this Login any Roles. It is just a plain ol' user
> (public).

> 2) I went to the server instance name at the top and GRANTed "Connect
> SQL" permission to the new Login just created;

> 3) I went to the database and granted "Connect" permission to the
> Login.

> After these steps I get the error reported in my earlier post.

> I should add that I am not an SQL Server expert and first looked at
> the SQL Server Mgt. Studio Express BOL.They are absolutely useless.

> what is the difference between a Login and a User?

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
news:40d46vF19tm2gU1@individual.net...
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..

> the login has been mapped to a corresponding user..

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
news:40d46vF19tm2gU1@individual.net...
> hi Andrew,
> Andrew Chalk wrote:
>> Andrea: Thanks you for your detailed reply.
>>
>> 1) I created a login under the server->security folder.
>> I did not give this Login any Roles. It is just a plain ol' user
>> (public).
>
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
>> 2) I went to the server instance name at the top and GRANTed "Connect
>> SQL" permission to the new Login just created;
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
>> 3) I went to the database and granted "Connect" permission to the
>> Login.
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
>> After these steps I get the error reported in my earlier post.
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
>> I should add that I am not an SQL Server expert and first looked at
>> the SQL Server Mgt. Studio Express BOL.They are absolutely useless.
>
> :D
> download the full BOL, december released..
>
>> what is the difference between a Login and a User?
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
>
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present....
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
>
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply
>

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
news:40d46vF19tm2gU1@individual.net...
> hi Andrew,
> Andrew Chalk wrote:
>> Andrea: Thanks you for your detailed reply.
>>
>> 1) I created a login under the server->security folder.
>> I did not give this Login any Roles. It is just a plain ol' user
>> (public).
>
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
>> 2) I went to the server instance name at the top and GRANTed "Connect
>> SQL" permission to the new Login just created;
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
>> 3) I went to the database and granted "Connect" permission to the
>> Login.
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
>> After these steps I get the error reported in my earlier post.
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
>> I should add that I am not an SQL Server expert and first looked at
>> the SQL Server Mgt. Studio Express BOL.They are absolutely useless.
>
> :D
> download the full BOL, december released..
>
>> what is the difference between a Login and a User?
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
>
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present....
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
>
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply
>

"Andrew Chalk" <achalk@magnacartasoftware.com> wrote in message
news:99nof.33254$7h7.968@newssvr21.news.prodigy.com...
> That URL does not use the work 'Login', only 'User'.
>
> What is the difference between a login and a user?
>
> Thanks,
>
> Andrew
>
> "Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
> news:40dk3lF198jigU1@individual.net...
>> hi Andrew,
>> Andrew Chalk wrote:
>>>
>>> The phrase:
>>>> the login has been mapped to a corresponding user..
>>>
>>> Can you explain that.
>>> How did it happen?
>>> Who is the user?
>>
>> perhaps my english is not as good as I thought.. :D
>> perhaps here,
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4fol.asp,
>> the architectural design is more clear..
>> a SQL Server Login (both standard SQL Server login or a WinNT login) is
>> just the very first brick in the wall in order to access a SQL Server
>> instance... without that you can not log in and connect to a specified
>> instance... it has a server wide range...
>> a User is just a database user (can have the same name as the
>> corresponding Login [Login is now somehaw obsolete, to be replaced by
>> principals] but this is not mandatory even if it's recommended in order
>> to minimize troubles :D) that, at creation time, is mapped via it's sid
>> column (dbname.sys.sysusers.sid) to an existing login
>> (master.sys.server_principals.sid)
>>
>> SELECT u.name AS [Name in DB], u.hasdbaccess ,
>> p.name AS [Login Name]
>> FROM master.sys.server_principals p JOIN sys.sysusers u
>> ON p.sid = u.sid
>>
>> Principals
>> http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
>> DB Users
>> http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx
>>
>>> Is it correct to say that the user is an instance of utilizing a
>>> login? I.e. "users" have logins.
>>
>> Logins can be granted db access as database users, but they can even
>> not..
>>
>>>Some may use the same logins as
>>> others (for example several instances of an application). In which
>>> case each application trying to use SQL server is a user and their
>>> name/password pair correspond to their login?
>>
>> an application is not a user or a login... in certain case an application
>> role can represent this scenario
>> (http://msdn2.microsoft.com/en-us/library/ms190998.aspx)
>>
>>> Deleted user "fred" and login "fred".
>>> Recreated login Fred but made sure that I went to 'User Mapping' and
>>> checked the database that I wanted this Login to be able to access;
>>> Login was created and, like magic, a user appeared, also named
>>> 'fred', under the database.
>>
>> it's correct... you created a login... then you granted that login db
>> access via a database user (in the defined database) mapping it to the
>> original login ...
>> if you only create a login without grating him/her access to your desired
>> database(s), that login will only access it if the predefined "guest"
>> database user is available (and usually that db user is not available for
>> security reason)...
>> --
>> Andrea Montanari (Microsoft MVP - SQL Server)
>> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
>> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
>> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
>> interface)
>> --------- remove DMO to reply
>>
>
>

Andrew Chalk wrote:
>
> The phrase:
>> the login has been mapped to a corresponding user..
>
> Can you explain that.
> How did it happen?
> Who is the user?

> Is it correct to say that the user is an instance of utilizing a
> login? I.e. "users" have logins.

>Some may use the same logins as
> others (for example several instances of an application). In which
> case each application trying to use SQL server is a user and their
> name/password pair correspond to their login?

> Deleted user "fred" and login "fred".
> Recreated login Fred but made sure that I went to 'User Mapping' and
> checked the database that I wanted this Login to be able to access;
> Login was created and, like magic, a user appeared, also named
> 'fred', under the database.

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
news:40dk3lF198jigU1@individual.net...
> hi Andrew,
> Andrew Chalk wrote:
>>
>> The phrase:
>>> the login has been mapped to a corresponding user..
>>
>> Can you explain that.
>> How did it happen?
>> Who is the user?
>
> perhaps my english is not as good as I thought.. :D
> perhaps here,
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4fol.asp,
> the architectural design is more clear..
> a SQL Server Login (both standard SQL Server login or a WinNT login) is
> just the very first brick in the wall in order to access a SQL Server
> instance... without that you can not log in and connect to a specified
> instance... it has a server wide range...
> a User is just a database user (can have the same name as the
> corresponding Login [Login is now somehaw obsolete, to be replaced by
> principals] but this is not mandatory even if it's recommended in order to
> minimize troubles :D) that, at creation time, is mapped via it's sid
> column (dbname.sys.sysusers.sid) to an existing login
> (master.sys.server_principals.sid)
>
> SELECT u.name AS [Name in DB], u.hasdbaccess ,
> p.name AS [Login Name]
> FROM master.sys.server_principals p JOIN sys.sysusers u
> ON p.sid = u.sid
>
> Principals
> http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
> DB Users
> http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx
>
>> Is it correct to say that the user is an instance of utilizing a
>> login? I.e. "users" have logins.
>
> Logins can be granted db access as database users, but they can even not..
>
>>Some may use the same logins as
>> others (for example several instances of an application). In which
>> case each application trying to use SQL server is a user and their
>> name/password pair correspond to their login?
>
> an application is not a user or a login... in certain case an application
> role can represent this scenario
> (http://msdn2.microsoft.com/en-us/library/ms190998.aspx)
>
>> Deleted user "fred" and login "fred".
>> Recreated login Fred but made sure that I went to 'User Mapping' and
>> checked the database that I wanted this Login to be able to access;
>> Login was created and, like magic, a user appeared, also named
>> 'fred', under the database.
>
> it's correct... you created a login... then you granted that login db
> access via a database user (in the defined database) mapping it to the
> original login ...
> if you only create a login without grating him/her access to your desired
> database(s), that login will only access it if the predefined "guest"
> database user is available (and usually that db user is not available for
> security reason)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply
>

Roger Wolter[MSFT] wrote:

"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
news:%23ua27zdAGHA.740@TK2MSFTNGP12.phx.gbl...
>A SQL Server instance can contain many databases. A login is a piece of
>data stored in the master database that allows you to connect to the SQL
>Server instance. It stores something to allow you to identify yourself to
>SQL Server - either a password for SQL Server logins or your Windows
>credentials for integrated logins. Once you have successfully identified
>yourself to SQL Server and completed the connection, you need to access
>data in a database. To do this, an administrator must create a user object
>in the database you want to access and link it to your login. You can only
>access databases that you have users created for you in. The user is
>granted the permissions necessary for you to do what you need to do in the
>database.
>
> The reason for this duality is that you may have different permissions
> depending on which database you are accessing. For example you may have
> permissions to create and drop tables and update any data in the payroll
> database but only have read permissions for data in the accounts
> receivable database. You may not even be allowed to open the HR database.
>
> I hope this helps clarify the difference and explains why you need both.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Andrew Chalk" <achalk@magnacartasoftware.com> wrote in message
> news:99nof.33254$7h7.968@newssvr21.news.prodigy.com...
>> That URL does not use the work 'Login', only 'User'.
>>
>> What is the difference between a login and a user?
>>
>> Thanks,
>>
>> Andrew
>>
>> "Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
>> news:40dk3lF198jigU1@individual.net...
>>> hi Andrew,
>>> Andrew Chalk wrote:
>>>>
>>>> The phrase:
>>>>> the login has been mapped to a corresponding user..
>>>>
>>>> Can you explain that.
>>>> How did it happen?
>>>> Who is the user?
>>>
>>> perhaps my english is not as good as I thought.. :D
>>> perhaps here,
>>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4fol.asp,
>>> the architectural design is more clear..
>>> a SQL Server Login (both standard SQL Server login or a WinNT login) is
>>> just the very first brick in the wall in order to access a SQL Server
>>> instance... without that you can not log in and connect to a specified
>>> instance... it has a server wide range...
>>> a User is just a database user (can have the same name as the
>>> corresponding Login [Login is now somehaw obsolete, to be replaced by
>>> principals] but this is not mandatory even if it's recommended in order
>>> to minimize troubles :D) that, at creation time, is mapped via it's sid
>>> column (dbname.sys.sysusers.sid) to an existing login
>>> (master.sys.server_principals.sid)
>>>
>>> SELECT u.name AS [Name in DB], u.hasdbaccess ,
>>> p.name AS [Login Name]
>>> FROM master.sys.server_principals p JOIN sys.sysusers u
>>> ON p.sid = u.sid
>>>
>>> Principals
>>> http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
>>> DB Users
>>> http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx
>>>
>>>> Is it correct to say that the user is an instance of utilizing a
>>>> login? I.e. "users" have logins.
>>>
>>> Logins can be granted db access as database users, but they can even
>>> not..
>>>
>>>>Some may use the same logins as
>>>> others (for example several instances of an application). In which
>>>> case each application trying to use SQL server is a user and their
>>>> name/password pair correspond to their login?
>>>
>>> an application is not a user or a login... in certain case an
>>> application role can represent this scenario
>>> (http://msdn2.microsoft.com/en-us/library/ms190998.aspx)
>>>
>>>> Deleted user "fred" and login "fred".
>>>> Recreated login Fred but made sure that I went to 'User Mapping' and
>>>> checked the database that I wanted this Login to be able to access;
>>>> Login was created and, like magic, a user appeared, also named
>>>> 'fred', under the database.
>>>
>>> it's correct... you created a login... then you granted that login db
>>> access via a database user (in the defined database) mapping it to the
>>> original login ...
>>> if you only create a login without grating him/her access to your
>>> desired database(s), that login will only access it if the predefined
>>> "guest" database user is available (and usually that db user is not
>>> available for security reason)...
>>> --
>>> Andrea Montanari (Microsoft MVP - SQL Server)
>>> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
>>> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
>>> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
>>> interface)
>>> --------- remove DMO to reply
>>>
>>
>>
>
>