locked
I can't login into SQL Server RRS feed

  • Question

  • Hello.  This is my first post

    I downloaded Microsoft SQL Server Management Studio 2012 for my home Vista PC.  Everytime I try to attach a database, such as AdventureWorks2012, I keep getting the following error message:

    Failed to retrieve data for this request.  (Microsoft. SqlServer.Management.Sdk.Sfc)

    Additional information:

    An Exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    User 'guest' does not have permission to DBCC checkprimaryfile.  (Microsoft SQL Server, Error: 2571

    I am logged in as an administrator on my computer, so why is SQL Server recognize me as a "guest" rather as an administrator?  Also, I can't even change the Server Authentication to SQL Server Server and Windows Authentication mode without getting an error message.  My defualt server name is 'McKinney-PC/SQLEXPRESS'.  Here is the error message

    Alter failed for Server 'McKinney-PC/SQLEXPRESS'.  (MIcrossoft.SqlServer.Smo)

    Additonal information:

    An Exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    The EXECUTE poermission was denied on the object 'xp_instance_regwrite', database 'mssqlsystemresource, scheme 'sys'. (Microsoft SQL server, Error:  229)

    This is very frustrating! What am I doing wrong?

    Thanks.

    Tuesday, November 26, 2013 5:36 PM

Answers

  • Hi,

    SQL Server 2008 onwards Builtin\Administrators group does not get sysadmin previleges to the database server by default. You need to explicitly provide privileges to the role.

    In your case if the SQL Server is in mixed authentication mode then login to the database server using the SA credentials and then try to attach the database files.

    Execute the below to provide the Administrators group with the sysadmin privileges:

    USE [master]
    GO
    CREATE LOGIN [MachineName\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    EXEC master..sp_addsrvrolemember @loginame = N'MachineName\Administrators', @rolename = N'sysadmin'
    GO


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    • Proposed as answer by Sofiya Li Wednesday, November 27, 2013 8:14 AM
    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:56 AM
    Tuesday, November 26, 2013 5:57 PM
  • Alter failed for Server 'McKinney-PC/SQLEXPRESS'. 

    The subject is "can't login", is that the issue or an other one? Is that above really the SQL Server name you used to logon?

    Machine and instance name must be separated with a backslash \, not with a slash /, so use

    McKinney-PC\SQLEXPRESS

    instead


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Wednesday, November 27, 2013 8:14 AM
    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:56 AM
    Tuesday, November 26, 2013 6:41 PM

All replies

  • Hi,

    SQL Server 2008 onwards Builtin\Administrators group does not get sysadmin previleges to the database server by default. You need to explicitly provide privileges to the role.

    In your case if the SQL Server is in mixed authentication mode then login to the database server using the SA credentials and then try to attach the database files.

    Execute the below to provide the Administrators group with the sysadmin privileges:

    USE [master]
    GO
    CREATE LOGIN [MachineName\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    EXEC master..sp_addsrvrolemember @loginame = N'MachineName\Administrators', @rolename = N'sysadmin'
    GO


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    • Proposed as answer by Sofiya Li Wednesday, November 27, 2013 8:14 AM
    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:56 AM
    Tuesday, November 26, 2013 5:57 PM
  • Alter failed for Server 'McKinney-PC/SQLEXPRESS'. 

    The subject is "can't login", is that the issue or an other one? Is that above really the SQL Server name you used to logon?

    Machine and instance name must be separated with a backslash \, not with a slash /, so use

    McKinney-PC\SQLEXPRESS

    instead


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sofiya Li Wednesday, November 27, 2013 8:14 AM
    • Marked as answer by Sofiya Li Monday, December 2, 2013 9:56 AM
    Tuesday, November 26, 2013 6:41 PM