|
|
|
date: Thu, 2 Jul 2009 19:23:19 +0200,
group: microsoft.public.sqlserver.server
back
Re: problem with new login
Ben (ben@dd.fg) writes:
> i defined a new login 'test' for Management Studio sql server express
> 2008. This account must be able to connect via Sql server
> Authentification and must be able to access database 'dbtest', but not
> the others. There are three databases: 'db1', 'db2' and 'dbtest'.
>
> Here are its properties:
> server roles: serveradmin
> user mapping for 'dbtest': dbowner (nothing for the other two databases).
>
> Now, I can connect to sql server with that account, but none of the
> databases are visible.When i click on Databases, i get this error:
> The server principal "test" is not able to access the database "db1" under
> the current security context. (Microsoft SQL Server, Error: 916)'
>
> Nothing mentioned about 'db2'!.
>
> What's the problem and how to solve this?
Apparently db1 (and maybe db2) is set to autoclose. Mgmt Studio then
thinks it has to startup that database to read some information that
is not available in sys.databases.
In Object Explorer Details (F7), remove Collation from the databases
view (right-click on the header), this should solve the issue.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
date: Thu, 02 Jul 2009 15:17:23 -0700
author: Erland Sommarskog
Re: problem with new login
Hi Erland,
it works. Thanks. Can you explain me briefly why it works now, i mean,
what's the purpose of 'Collation' and why must it be turned off ... Just to
know.
Thanks.
"Erland Sommarskog" schreef in bericht
news:Xns9C3D2FD3C5DFYazorman@127.0.0.1...
> Ben (ben@dd.fg) writes:
>> i defined a new login 'test' for Management Studio sql server express
>> 2008. This account must be able to connect via Sql server
>> Authentification and must be able to access database 'dbtest', but not
>> the others. There are three databases: 'db1', 'db2' and 'dbtest'.
>>
>> Here are its properties:
>> server roles: serveradmin
>> user mapping for 'dbtest': dbowner (nothing for the other two databases).
>>
>> Now, I can connect to sql server with that account, but none of the
>> databases are visible.When i click on Databases, i get this error:
>> The server principal "test" is not able to access the database "db1"
>> under
>> the current security context. (Microsoft SQL Server, Error: 916)'
>>
>> Nothing mentioned about 'db2'!.
>>
>> What's the problem and how to solve this?
>
> Apparently db1 (and maybe db2) is set to autoclose. Mgmt Studio then
> thinks it has to startup that database to read some information that
> is not available in sys.databases.
>
> In Object Explorer Details (F7), remove Collation from the databases
> view (right-click on the header), this should solve the issue.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
date: Fri, 3 Jul 2009 11:43:08 +0200
author: Ben
Re: problem with new login
Ben (qcqcqs@qcqscqc.df) writes:
> it works. Thanks. Can you explain me briefly why it works now, i mean,
> what's the purpose of 'Collation' and why must it be turned off ... Just
> to know.
Each database has a collation, which is the default for new columns in
that database. A collation is a set of rules that controls sorting and
comparison of character data, and also rules for upper()/lower() functions
etc.
The default setting in SSMS is that when you list the databases in
Object Explorer details, it includs the collation. As long as all databases
are online, this is no problem. But if a database is offline, the collation
is not recorded in sys.databases. If the database is entirely offline,
SSMS does not try to access it. However, there is a setting autoclose,
which closes a database if no one has accessed it for a while. This setting
is on for new databases on Express Edition, if memory serves. When SSMS
finds that the database is auto-closed, SSMS accesses the database to
get the collation. But if the user does not have access to the database,
this fails.
When I think of it, I think this really silly bug is fixed in SP1 for
SQL 2008, so you may want to get your hands on that service pack. (Well,
you should anyway. There were at least one really grave bug in the
RTM release.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
date: Fri, 03 Jul 2009 15:40:28 -0700
author: Erland Sommarskog
Re: problem with new login
Thanks
"Erland Sommarskog" schreef in bericht
news:Xns9C3E6E9ADA19Yazorman@127.0.0.1...
> Ben (qcqcqs@qcqscqc.df) writes:
>> it works. Thanks. Can you explain me briefly why it works now, i mean,
>> what's the purpose of 'Collation' and why must it be turned off ... Just
>> to know.
>
> Each database has a collation, which is the default for new columns in
> that database. A collation is a set of rules that controls sorting and
> comparison of character data, and also rules for upper()/lower() functions
> etc.
>
> The default setting in SSMS is that when you list the databases in
> Object Explorer details, it includs the collation. As long as all
> databases
> are online, this is no problem. But if a database is offline, the
> collation
> is not recorded in sys.databases. If the database is entirely offline,
> SSMS does not try to access it. However, there is a setting autoclose,
> which closes a database if no one has accessed it for a while. This
> setting
> is on for new databases on Express Edition, if memory serves. When SSMS
> finds that the database is auto-closed, SSMS accesses the database to
> get the collation. But if the user does not have access to the database,
> this fails.
>
> When I think of it, I think this really silly bug is fixed in SP1 for
> SQL 2008, so you may want to get your hands on that service pack. (Well,
> you should anyway. There were at least one really grave bug in the
> RTM release.)
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
date: Sat, 4 Jul 2009 22:14:00 +0200
author: Ben
|
|
