locked
SQL Server - Permissions RRS feed

  • Question

  • Hey

    Sorry if this may be in the wrong forum, i couldnt find one that related..

    I am admin to my own PC but whenever i try to add a user to my SQL server through MSSMS, i always get an error saying i do not have permissions. I had the same when i was trying to work on a database remotely for a friend. I was albe to login, create the database then didnt have permission to edit it!

    What settings am i missing? Is there something specific i should be doing to allow myself to give myself permissions?

    Here is the error. I made a temp AppUser user to test.

    i dont have a sa user SQL account and when i tried to add it, i got an error saying i couldnt.

    Any advice would be appreciated :)

    Thanks

    Nacho


    I am here to ask questions and learn from others. Cor Ligthert DO NOT REPLY TO ANY OF MY POSTS!!

    Thursday, November 5, 2020 9:36 PM

All replies

  • Probably should try one of the sql forums.

    But, how are you connecting using SSMS? Since this is your local computer, then I assume you simply connecting as a windows authenticated user?

    And we assume that your local copy of sql server is running in mixed mode? (that is required to allow you create new logons, and THEN allow you to create a user.

    So, in sql server, you have the concept of 

    SQL logon. this allows you to log into the server - not yet a database.

    So this gets you into the entrance of the building - the lobby.

    Once you are in, then you have what is called a "user".

    A user is something attached to each database in question.

    So, you will need BOTH. In your screen shot, it says that AppUser is not valid.

    But, did you FIRST create the SQL logon? You have to create that SQL Logon first, or at least specify a existing one.

    For the most part, I have always make the SQL logon, and the User logon the SAME, but you don't have to.

    So note careful when  you expand the sql tree view. You can expand all databases, but before you do, note this:

    So you need to create a SQL logon first. Note how it not attached to any database.

    now, if we expand the database, you note for EACH database, there is a "user".

    So you have this:

    So you need to first create a logon. Then you can create a user.

    You see both here:

    So, when you add a user to a given database, you can (and should) specify the sql logon to use.

    It thus follows that you need/want/should/good idea to create the SQL logon first. As noted, it is common practice to have the SQL logon the same as the "user" for a given database.

    I would as noted consider asking this question in a sql form - it not really a vb.net question.

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada


    Thursday, November 5, 2020 10:39 PM
  • Hi

    an excellent very well described answer, much appreciated. I did add the logon user first (or so i thought), there was an error popup window appearing 'behind' management studio. I have it figured out now thanks

    FYI

    I did post into an SQL server forum as seen here

    but it somehow managed to get posted somewhere else...

    Thanks again

    Nacho


    I am here to ask questions and learn from others. Cor Ligthert DO NOT REPLY TO ANY OF MY POSTS!!

    Thursday, November 5, 2020 10:50 PM
  • SQL SERVER - permissons is YOUR name and title of this post.

    The forum you are posting into is Visual Basic

    eg here:

    So SQL server is YOUR typing and the name/title of the post in the forum Visual Basic.

    Not a huge deal - as you note, you posting in the wrong spot - but the title YOU give to YOUR post is whatever you want - including in this case SQL Server. But that really only the name/title of your post - you can type anything you want for that title - it will go into the forum you are posting into.

    As noted - looks like  you have things sorted out, so no problem - best of luck!

    Regards,

    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    Thursday, November 5, 2020 11:10 PM
  • Hi NachoShaw,

    SQL Server forum has moved to Q&A, and you can ask your question in the following forum.

    sql-server-general(Q&A)

    Thank you for your understanding.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 6, 2020 1:27 AM
  • Hi

    Thanks for the reply. This was the site i was on when i made my post

    anyways, its been moved now. Thanks so much for your help :)

    Nacho


    I am here to ask questions and learn from others. Cor Ligthert DO NOT REPLY TO ANY OF MY POSTS!!

    Friday, November 6, 2020 6:30 PM