Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Determining minimum Permissions Needed Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 4:55 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:50 PM
Points: 550, Visits: 1,611
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?

Post #1396436
Posted Thursday, December 13, 2012 10:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1396478
Posted Thursday, December 13, 2012 11:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:49 AM
Points: 251, Visits: 602
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
Post #1396483
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse