Post reply

New User cannot connect to the database

  • July 13, 2017 at 4:03 am

    #402285

    Hi all, I know very little about SQL Server but so far we have been managing to login to our DB via the servers Administrator account. Now we are required to not use this and use proper users' Windows accounts. So I have created a new user in Databases>Our Database>Security>Users

    The new user (lets say john.smith) can now get past the 'cannot open database requested by the login' error. However, they are now getting... 

    'There was a problem connecting to the database. The exception was The EXECUTE permission was denied on the object 'getalerts', database 'OurDatabaseName', schema 'dbo'

    This default schema for this user was Blank but after the first login attempt this field has now changed to 'dbo'

    There are a lot of tick boxes (I'm looking at Database Role Membership) but I don't want to go ticking everything when its not recommended.

    Would someone be able to provide some advice please?

    Thanks, John

  • July 13, 2017 at 4:08 am

    #1950709

    Looks like you've got a logon trigger in the system.

    Try running this in the DB OurDatabaseName
    GRANT  EXECUTE ON getalertsgetalerts TO <put the DB user name here>

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 13, 2017 at 4:22 am

    #1950710

    Hi, thank you very much for the reply. While I have no doubt that that will work, I'm concerned that this will be the first in a long line of these types of things that I will have to keep coming back to allow execute (and I will be adding a few more users today hopefully as well). Is there a way of giving this user the same access as whatever the 'Administrator' was getting (In the SQL users there was no Administrator. Only dbo, guest,INFORMATION_SCHEMA, sys) ?

    Thanks again, John

  • July 13, 2017 at 4:56 am

    #1950714

    Sure, make them a member of the sysadmin role, but then you may as well just go back to using the Administrator account, as that's essentially what you'll have done.

    Whoever told you not to use the Administrator account almost certainly did not intend for you to create a TotallyNotAnAdministrator user with admin-level permissions.
    Principal of least privilege. Users get the absolute minimum permissions they need to do their work and absolutely nothing else. Yes, getting your security right will be a pain, rather now than after someone walks off with the DB because of lax permissions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 13, 2017 at 5:06 am

    #1950715

    Hi, thanks again for the reply. Just as you sent this I have managed to get this working. I've right-clicked MyDatabaseName>Properties>Permissions>*Selected the user*> and under the Grant column I have ticked

    Connect (This was already seleted)
    Execute
    Select
    Update (For some users I will untick this as they will need read-only access but for this user they will need to make changes)

    I've left all the other items blank and I can not login with seemingly no issues. Does this sound like a better alternative that giving the user sysadmin role?

    I have attached a screenshot of the permissions bit. Thank you for your help on this.

  • July 13, 2017 at 5:34 am

    #1950718

    If you're adding more users, with all the same (basic) permissions, it sounds like a better option would be to create a database role. Then you can give the role access to all of these triggers, etc, and add to user to the role.

    If you then discover later that they all need access to another object, or another trigger is causing problems, you give the role the correct rights, and all the users will gain it, rather than having to alter each one individually.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • July 13, 2017 at 7:28 am

    #1950731

    Hi thanks for that tip. At the moment it is going to be less than 5 users and I'm 99.9% confident that their permissions will not change so I'm happy to set up individually but that is very useful to know for future.

    Thanks, John

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply