March 25, 2010 at 4:31 pm
Hello,
When I click on properties of a database, users, tables, or any object within SQL Server Management Studio, I get this error:
Cannot show requested dialog.
Cannot open user default database. Login failed.
Login failed for user 'PBTF\bob'. (Microsoft SQL Server, Error: 4064)
I have set the default database to the master, by selecting Options on the Connect to Server dialog and ran this command: ALTER LOGIN 'PBTF\bob WITH DEFAULT_DATABASE = master.
SQL Server says:
Cannot alter the login 'PBTF\bob', because it does not exist or you do not have permission.
'PBTF\bob' account is in a security group, which has been added to SQL Server for Server Access. 'PBTF\bob' and the security group are not added to the database security. Security group has admin rights in SQL Server.
Despite all this, I still experience the error. Any tips on how I can fix this?
Thanks
March 25, 2010 at 4:59 pm
Cannot show requested dialog.
Cannot open user default database. Login failed.
Login failed for user 'PBTF\bob'. (Microsoft SQL Server, Error: 4064)
I think the error is what is: cannot open your default database.
Does the default database still exist?
You can probably go in via sqlCmd and change your default database.
sqlcmd -E -d master
then issue your alter login statement.
March 25, 2010 at 5:32 pm
The default database is master and it still exists.
I get this when running the commands:
C:\>sqlcmd -E -d master
1> ALTER LOGIN pbtf\bob WITH DEFAULT_DATABASE = master
2> go
Msg 170, Level 15, State 1, Server THOTH, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I am logging in as bob with Windows Authenication. As mentioned before, bob's accont is in the Active Directory security group at the SQL Server level.
March 25, 2010 at 5:56 pm
paulc.byrum (3/25/2010)
The default database is master and it still exists.I get this when running the commands:
C:\>sqlcmd -E -d master
1> ALTER LOGIN pbtf\bob WITH DEFAULT_DATABASE = master
2> go
Msg 170, Level 15, State 1, Server THOTH, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I am logging in as bob with Windows Authenication. As mentioned before, bob's accont is in the Active Directory security group at the SQL Server level.
I think the Incorrect Syntax error is because you have not delimited the login name, try [pbtf\bob].
You should be able to change the default database for your login.
That being said, I don't follow. If your default database is already master...
March 25, 2010 at 6:55 pm
'PBTF\bob' account is in a security group, which has been added to SQL Server for Server Access.
Sorry, I just noted your statement that you are in a group. Your default database should be set at group level. You may want to check if anyone else in the group is getting the same error and change the default database at the group level.
Security group has admin rights in SQL Server.
This, however, should mean that you have access to all the databases on the Server because sysadmin is mapped to dbo for all databases on the instance.
March 26, 2010 at 10:22 am
I am trying with the security group name now.
ALTER command says Line 1: Incorrect syntax near 'LOGIN'.
C:\>sqlcmd -E -d master
1> ALTER LOGIN [PBTF\Group - IT DB Admins] WITH DEFAULT_DATABASE = master
2> go
Msg 170, Level 15, State 1, Server THOTH, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
1>
Any suggestions?
March 26, 2010 at 10:33 am
Perhaps, my problem is that I have several instanaces of SQL Server. They are:
SQL Server 9.0.4035 (SharePoint instance)
SQL Server 8.0.2055 (Standard edition)
SQL Server 8.0.760 (MSDE)
SQL Server 9.0.4053 (Express Edition)
The instance with the problem is "SQL Server 9.0.4035 (SharePoint instance)".
If I am using the sqlcmd, how do I ensure I am using the correct instance?
Is the ALTER LOGIN command availble in this instance?
March 26, 2010 at 6:53 pm
Is the ALTER LOGIN command availble in this instance?
If you are getting the "Incorrect Syntax" error, You may want to double check the instance you are running the ALTER LOGIN command against. It is not available in Sql Server 2000.
If I am using the sqlcmd, how do I ensure I am using the correct instance?
Typing sqlcmd -? at the command prompt will list all the options/switches.
You can specify a server to connect to using the -S switch (-S servername).
I will suggest that you check with other users in the group to see if they are having similar problems before you change the default database.
March 27, 2010 at 1:11 pm
Please check the the login still exists in the database as a user.
March 28, 2010 at 5:52 am
what info does the event log show?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 29, 2010 at 11:28 am
The login does exist in the security group.
In the event log, the event occurs when the error message occurs in SQL Server Management Studio:
The description for Event ID ( 18456 ) in Source ( MSSQL$MICROSOFT##SSEE ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer.
I ran the following command and there were no errors. It seemed to work, but I tried viewing properties in Management Studio and the error still occurs:
C:\>sqlcmd -E -d master -S THOTH\MICROSOFT##SSEE
1> ALTER LOGIN [PBTF\Group - IT DB Admins] WITH DEFAULT_DATABASE = master
2> go
1>
March 29, 2010 at 1:01 pm
What error do you get now? Is everyone in that group have the same error/problem when accessing Sql Server?
March 29, 2010 at 2:38 pm
Keep in mind that if a use belongs to multiple groups, the default database setting cannot be used !
Check the access paths and memberships:
Use yourdb
go
-- how does this login get to the database (authority paths)
EXEC master..xp_logininfo @acctname = 'mydomain\mylogin',@option = 'all'
go
-- who is member of this group
EXEC master..xp_logininfo @acctname = 'mydomain\mygroup',@option = 'members'
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2010 at 3:18 pm
When I run the following in SSMS, I get:
-- how does this login get to the database (authority paths)
EXEC master..xp_logininfo @acctname = '[PBTF\Group - IT DB Admins]',@option = 'all'
go
Results:
Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user '[PBTF\Group - IT DB Admins]', error code 0x534.
-- who is member of this group
EXEC master..xp_logininfo @acctname = '[PBTF\Group - IT DB Admins]',@option = 'members'
go
Results:
The query executed successfully, but nothing was return. No rows.
March 29, 2010 at 3:58 pm
I took off the [] and got results. PBTF\bob is in the security group.
account name type privilege mapped login name permission path
---------------------------------------------------------------------------------------
PBTF\Group - IT DB AdminsgroupadminPBTF\Group - IT DB AdminsNULL
account name type privilege mapped login name permission path
--------------------------------------------------------------------------------------
PBTF\bob user admin PBTF\bob PBTF\Group - IT DB Admins
PBTF\AStan user admin PBTF\AStan PBTF\Group - IT DB Admins
PBTF\AYalley user admin PBTF\AYalley PBTF\Group - IT DB Admins
PBTF\GTrap user admin PBTF\GTrap PBTF\Group - IT DB Admins
PBTF\PBrooks user admin PBTF\PBrooks PBTF\Group - IT DB Admins
PBTF\SvcSPContent user admin PBTF\SvcSPContent PBTF\Group - IT DB Admins
PBTF\SvcSPSearch user admin PBTF\SvcSPSearch PBTF\Group - IT DB Admins
PBTF\SvcSPSQL user admin PBTF\SvcSPSQL PBTF\Group - IT DB Admins
PBTF\SvcSQL user admin PBTF\SvcSQL PBTF\Group - IT DB Admins
PBTF\SvcSQLRS user admin PBTF\SvcSQLRS PBTF\Group - IT DB Admins
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply