|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
problem with new login
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 Ben (b**@dd.fg) writes:
Show quoteHide quote > i defined a new login 'test' for Management Studio sql server express Apparently db1 (and maybe db2) is set to autoclose. Mgmt Studio then > 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? 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, esq***@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 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. Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> schreef in bericht news:Xns9C3D2FD3C5DFYazorman@127.0.0.1... > Ben (b**@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, esq***@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 > Ben (qcq***@qcqscqc.df) writes:
> it works. Thanks. Can you explain me briefly why it works now, i mean, Each database has a collation, which is the default for new columns in> what's the purpose of 'Collation' and why must it be turned off ... Just > to know. 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, esq***@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 Thanks
Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> schreef in bericht news:Xns9C3E6E9ADA19Yazorman@127.0.0.1... > Ben (qcq***@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, esq***@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 >  
Other interesting topics
Autostatistic ON/OFF on separate tables?
sql server management studio to connect to database Install SQL Server 2008 Questions .... Saving images Column Limits Problem with triggers an ntext SQLhas encountered 2 occurrence(s) of IO requests taking longer th sql replication SQL Server Programming Books database 'tempdb' is full: Error: 9002, Severity: 17, State: 6 |
|||||||||||||||||||||||
