SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Determining minimum Permissions Needed


Determining minimum Permissions Needed

Author
Message
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3515 Visits: 2000
We have a process which imports data from a downloaded MDB file to a SQL database. The import is performed by software supplied by the vendor of the applicaction software that used the database. The import uses a SQL user created just for the import.

On the production server, this user is in the sysadmin Server Role. The process runs successfully, but I think sysadmin is too many rights.

Using a non-production copy, I am trying to determine the minimum rights needed to be granted to the SQL user for the process to run, Although the SQL user is now in the db_owner database role for the database that needs the import, I still get an error which looks like a permissions:

Import Failed
Table or indexed view '<table name>' does not have a full-text index or user does not have permission to perform this action.

Is there a good way to determine what are the minimum rights needed to perform a an action - particularly when you don't have access to read the actual SQL that is run?
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38850 Visits: 14411
But you do have access to read the SQL that is run. You can setup an Extended Events session or a Server-side Trace to capture all SQL statements issued against the server when you run the process in non-prod. You could do this while the process runs as a sysadmin to see all commands, or run it as only a db_owner where the last (or close to the last) statement to start will be the one that causes the permissions error ;-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
jeetsingh.cs
jeetsingh.cs
Mr or Mrs. 500
Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)Mr or Mrs. 500 (561 reputation)

Group: General Forum Members
Points: 561 Visits: 618
See your error shows that user donot have permission to execute FULLTEXT
so you can give permission for the same.
refer to this link for further reference

http://msdn.microsoft.com/en-us/library/ms190502.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search