July 13, 2017 at 4:03 am
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
'There was a problem connecting to the database. The exception was The EXECUTE permission was denied on the object '
This default schema for this user was Blank but after the first login attempt this field has now changed to '
There are a lot of tick boxes (I'm looking at Database Role Membership) but I don't want to go ticking everything when
Would someone
Thanks, John
July 13, 2017 at 4:08 am
Looks like you've got a logon trigger in the system.
Try running this in the DB OurDatabaseNameGRANT 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
July 13, 2017 at 4:22 am
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
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
July 13, 2017 at 5:06 am
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
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
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