CODEWITHSUNDEEP
sql-serverstored-proceduresplesk
user192618
user192618

Reputation: 99

Grant users permission to stored procedures

I'm not very knowledgeable of ASP.NET or MS SQL at all, but was asked to migrate a website based on ASP.NET to a new host. I am moving the site to a HostGator Windows Shared account.

There is a small admin section of the site that has a MS SQL database behind it. I obtained a copy of this database in a .bak file from the previous host.

I was a able to create a MS SQL database in HostGator and import the backup file into it. The database came with a user that was being used to connect to the database from the website.

Although before being able to connect to the DB with this user, HostGator forced me change it's username to conform to their format of "HostGatorUserName_UserName".

Then after successfully connecting to the DB with this user, I got the "Cannot find stored procedure" error. I found out this is probably due to the user not having permission to access any of the stored procedures it did before. I believe this is probably due to the username having been changed.

Now I have no idea how to restore permissions to these stored procedures to the user. HostGator does not provide any web admin for MS SQL although I was able to connect to the database (with the same user) with Oracle SQL Developer (I'm on a Mac). I've googled how to grant permissions to a stored procedure to a user, but of course I cannot grant permissions to myself. I can't find anywhere in Plesk on HostGator where I can manage permissions for existing users or ones I create.

Upvotes: 0

Views: 3301

Answers (1)

anon
anon

Reputation:

If this is the only login HostGator has provided you, then they need to elevate the rights (probably by adding the user to the dbo role). This may not be what you want, but it's not clear to me what was in place before this database got restored. Did you have a login to the server that could access other databases?

If they also gave you a different login to connect with, that login is probably in the dbo role. So you can login as that user and then:

GRANT EXEC ON dbo.procedure_name TO HostGatorUserName_UserName;

(Sorry but I don't know anything about Plesk. And you'll probably get better answers if you talk to HostGator support to get your access rights straightened out.)

Upvotes: 1

Related Questions