none
SQL Server Permission after installation RRS feed

  • Question

  • Hi,

    I installed SQL Server 2008 R2 with an account. Let me call it SqlSvc. Windows Authentication.

    Installation went fine. I installed SharePoint on top of this database.

    I am facing problem with backup/ retores of various degree. Firstly, what is the one account in the above SQL Server that can like do everything? Like a superuser who has all access. I thought that would be SqlSvc. But I can not take backup of some databases, I can not add another account to a certain database from this account.


    Thanks, Soumya | MCITP, SharePoint 2010

    Thursday, May 31, 2012 9:58 PM

Answers

All replies

  • Are you using SQL or windows authentication ?

    can you run the below query to know who are the SYSADMINs ?

    SELECT NAME, isntname FROM SYSLOGINS
    WHERE sysadmin = 1 and hasaccess = 1


    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Thursday, May 31, 2012 10:48 PM
    Moderator
  • You initially assign Sysadmins during the installation process. It is possible to add a sysadmin after installation, see this post for details - http://sqlblog.com/blogs/argenis_fernandez/archive/2011/07/10/think-your-windows-administrators-don-t-have-access-to-sql-server-2008-by-default-think-again.aspx


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich

    • Proposed as answer by Peja Tao Friday, June 1, 2012 6:29 AM
    • Unproposed as answer by Soumya B Monday, June 4, 2012 2:19 PM
    • Marked as answer by Soumya B Monday, June 4, 2012 4:35 PM
    Friday, June 1, 2012 12:00 AM
  • Thanks to both of you.

    "Are you using SQL or windows authentication ?" Windows.

    I will revert back in a day checking all you told me to check.


    Thanks, Soumya | MCITP, SharePoint 2010

    Friday, June 1, 2012 1:57 AM
  • By assigning the Sqlsvc account to the sql server services, you are only part way there.  You'll need to give the SqlSvc account appropriate rights on the server itself.  For example, if you want the SQL Agent to automatically take a backup, the SqlSvc account will need rights to the folders on the OS into which the backup will be written.  Some folk add an account like SqlSvc to the local server's Administrator group.  Some other folk decide to take a more granular approach and allow permissions on an as-needed basis to any OS level objects.

    HTH.

    • Edited by Don_Don Friday, June 1, 2012 4:16 PM
    Friday, June 1, 2012 4:15 PM
  • Sorry, I am a bit late picking up this thread. But let me try to reengage in answering these questions:

    Ahmed, here is what I get when I execute the query you suggested. Although I don't know what to make out of this. Does it mean there is no domain user with the sysadmin role?

    sa 0
    NT AUTHORITY\SYSTEM 1
    NT SERVICE\MSSQLSERVER 1
    NT SERVICE\SQLSERVERAGENT 1

    I am able to login to ssms. However, if I try to click on any database that I restored, I get messages like this:

    So, my next step is to give an account dbowner right to the database it is complaining about. But I can not see the database in the User Mapping. Hope this gives the answerers more clue.


    Thanks, Soumya | MCITP, SharePoint 2010


    • Edited by Soumya B Monday, June 4, 2012 2:50 PM Corrected typo
    Monday, June 4, 2012 2:26 PM
  • Hi Richard. I was going through the link you provided. I do not have sysinternals in this box. And since this is client's box, I can not have it installed. So, to open command prompt as nt authority\system, I tried good old:

    runas /user:"nt authority\system" cmd

    and immediately got asked the password of the nt authority\system. Who will know this pwd. Will IT, who installed the OS on this box know it?


    Thanks, Soumya | MCITP, SharePoint 2010

    Monday, June 4, 2012 3:17 PM
  • OK could get around this using backdoor.

    By looking up the information @

    http://mikehowells.wordpress.com/2011/02/12/running-a-command-prompt-as-nt-authoritysystem/

    and then by opening ssms from command prompt, I could logon to ssms using nt authority\system and then I could add Sqlsvc as sysadmin. This user can add other users as sysadmin.

    This solution is similar to Richard's suggestion. Just working from command prompt seemed simpler to me and I could follow along. Had to use sysinternals nonetheless which I did not want. Thanks again everyone.


    Thanks, Soumya | MCITP, SharePoint 2010

    • Marked as answer by Soumya B Monday, June 4, 2012 4:35 PM
    Monday, June 4, 2012 4:34 PM