Post reply

User Security Settings Issue - limiting access - Import and Export Wizard

  • October 24, 2006 at 10:34 am

    #153251

    I am currently managing a SQL 2005 server that is not a part of a domain so it is accessed using SQL login.  I need to setup a user so that he has access to one database and is able to manage and in particular run the SQL Server Import and Export Wizard.

    When he tries to run it he gets this error:

    Error Message

     

    I have him setup with these credentials =

    MSDB: db_datareader, db_datawriter,

    DATABASE_1:  db_accessadmin, db_datareader, db_datawriter, db_ddladmin, db_owner, db_securityadmin

     

    He is logging in remotely using the SQL 2005 Management Studio.  Any ideas?

    New to the DBA world...thank you for your help!,

    IanR

  • October 24, 2006 at 11:04 am

    #667645

    You have to add this person to DTS roles. Here is my experiment that shows that only User Ids for users or roles 10,11 and 12 have Execute permission on this procedure that is mentioned in the error message. See the last query that shows what roles you should add your user.

    select

    name, id from sysobjects where name = 'sp_dts_checkexists'

    sp_dts_checkexists 39671189

    select

    id, grantee, grantor from syspermissions where id = 39671189

    39671189 10 1

    39671189 11 1

    39671189 12 1

    select * from sys.database_permissions where major_id = 39671189

    1 OBJECT_OR_COLUMN 39671189 0 10 1 EX   EXECUTE G GRANT

    1 OBJECT_OR_COLUMN 39671189 0 11 1 EX   EXECUTE G GRANT

    1 OBJECT_OR_COLUMN 39671189 0 12 1 EX   EXECUTE G GRANT

    select

    UID, Name from sysusers where UID IN (10,11,12)

    10 db_dtsadmin

    11 db_dtsltduser

    12 db_dtsoperator

    Regards,Yelena Varsha

Viewing 2 posts - 1 through 1 (of 1 total)

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