problem with new login

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ben - 02 Jul 2009 18:23 GMT

Hi,

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?
Thanks
Ben

Reply to this Message

Erland Sommarskog - 02 Jul 2009 23:17 GMT

> i defined a new login 'test' for Management Studio sql server express
> 2008. This account must be able to connect via Sql server
[quoted text clipped - 13 lines]
>
> 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.

Signature

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

Reply to this Message

Ben - 03 Jul 2009 10:43 GMT

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.

>> i defined a new login 'test' for Management Studio sql server express
>> 2008. This account must be able to connect via Sql server
[quoted text clipped - 21 lines]
> In Object Explorer Details (F7), remove Collation from the databases
> view (right-click on the header), this should solve the issue.

Reply to this Message

Erland Sommarskog - 03 Jul 2009 23:40 GMT

> 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.)

Signature

Reply to this Message

Ben - 04 Jul 2009 21:14 GMT

Thanks

>> 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
[quoted text clipped - 23 lines]
> you should anyway. There were at least one really grave bug in the
> RTM release.)

Reply to this Message