Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2006
    Posts
    54

    Unanswered: Turn Off Import / Export Data

    Hi,

    I have a SQL Server 2005 database that I have created several users with db_datareader access.

    Most of these users access the data via ODBC, but some use SQL Server.

    Those that can use SQL Server are my issue.
    I assumed that with db_datareader users could only connect and view the data (SELECT) but after testing, they can also IMPORT and EXPORT data?

    Is this right?
    How can I turn it off?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm going to take a punt on this.

    By use "SQL Server" I assume you mean use "SQL Server Management Studio". I think that clarification probably clears it up for you - it is not a SQL Server permissions issue but rather the functionality offered by SSMS. Import\ Export is I believe a sort of Integration Services wizard. In order to "disable" it you will need to do some customisation of SSMS. I started looking in to this some months ago but never really progressed it - it isn't trivial.

    That's my best guess anyway.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by pootle flump View Post
    By use "SQL Server" I assume you mean use "SQL Server Management Studio". I think that clarification probably clears it up for you - it is not a SQL Server permissions issue but rather the functionality offered by SSMS. Import\ Export is I believe a sort of Integration Services wizard. In order to "disable" it you will need to do some customisation of SSMS. I started looking in to this some months ago but never really progressed it - it isn't trivial.

    That's my best guess anyway.
    Yes, I do mean SSMS.

    If that is in deed the case, being able to Import a Table into a Database you don't have ownership of is quite a big thing....

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh hang on - I misread your post.
    You are right - this would be silly. The only thing I can imagine is that the import\ export authenticates using the SSIS service account. You could run profiler to check. SSMS is a very unusual tool to provide to people with read only access. Although not necessarily only for admins it is not really intended for that sort of use.

    Have you considered providing them with Query Analyser or another light weight tool instead? Even Access....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by pootle flump View Post
    Oh hang on - I misread your post.
    You are right - this would be silly. The only thing I can imagine is that the import\ export authenticates using the SSIS service account. You could run profiler to check. SSMS is a very unusual tool to provide to people with read only access. Although not necessarily only for admins it is not really intended for that sort of use.

    Have you considered providing them with Query Analyser or another light weight tool instead? Even Access....
    I'm not providing them anything, they already have the software, I only manage the database.
    The people accessing the data are Analysts, Developers etc...

    I ran the profiler, and logged in using the TestLogin which I can see in the LoginName section but not sure what I should be looking for?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - it was quicker to test myself than explain.

    When I run an import it authenticates using the details I supply to the wizard. I can see the wizard creating tables using my domain account. I have not tested using a dbreader only account however I do not now see how what you describe could be possible. Are you certain? How are you restricting these people to datareader?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've fully tested the whole thing - import export does not work using a read only account.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2006
    Posts
    54
    I did the following

    Created a Login and allowed it access to my Database
    In User Mappings Under Schema, I selected db_datareader

    Then created the user within the database itself selecting db_datareader as the Role.

    Importing and Exporting work fine?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I did the following:

    • I created a SQL Server login and mapped it to a user in a database and gave db_datareader access only.
    • I logged in to SSMS using this login.
    • I used the import\ export wizard on this database.
    • Even though I was logged in using a SQL Server login, the wizard defaulted to authenticating using windows authentication - I had to re-enter my SQL Server log in details ****I bet this is where your process is going wrong - do these people have access to the database via windows authentication?****
    • The wizard continued but warned me that the account I was using only had data reader permissions
    • When I executed the package it failed with lots of errors. Profiler indicated the last command it attempted to run was the create table statement
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is my script for creating the required objects:
    Code:
    USE master
    go
    
    DECLARE @sql            AS VARCHAR(8000)
          , @login_name     AS VARCHAR(100)
          , @password       AS VARCHAR(100)
          , @user_name      AS VARCHAR(100)
          , @db_name        AS VARCHAR(100)
        --Set to 1 if we are removing the user and login, 0 to create them
          , @clean_up_only  AS BIT
    
    --Login, user, password and database
    SELECT  @login_name     = 'readOnlyTest'
          , @password       = '5tr0nGP455w0rd'
          , @user_name      = 'readOnlyTest'
          , @db_name        = 'test'
          , @clean_up_only  = 0
    
    --Delete the login if it exists
    IF EXISTS    (SELECT NULL FROM sys.server_principals WHERE name = REPLACE(REPLACE(@login_name, ']', ''), '[', '')) BEGIN
    
        SELECT  @sql = 'DROP LOGIN ' + @login_name
    
        EXEC    (@sql)
        
        PRINT   @sql
    
    END
    
    IF @clean_up_only = 0 BEGIN
    
        --Create the login
        SELECT   @sql = 'CREATE LOGIN ' + @login_name + ' WITH PASSWORD = ''' + @password + ''''
    
        EXEC    (@sql)
    
        PRINT   @sql
    
    END
    
    --remove the user from [[@db_name]]
    SELECT   @sql = REPLACE('USE [[@db_name]]; IF EXISTS (SELECT NULL FROM sys.database_principals WHERE name = ''' + @user_name + ''') DROP USER ' + @user_name + ';', '[@db_name]', @db_name)
    
    EXEC    (@sql)
    
    PRINT   @sql
    
    IF @clean_up_only = 0 BEGIN
    
        --Add the user to [[@db_name]].
        SELECT   @sql = REPLACE('USE [[@db_name]]; CREATE USER ' + @user_name + ' FOR LOGIN ' + @login_name + ';', '[@db_name]', @db_name)
    
        EXEC    (@sql)
    
        PRINT   @sql
    
        --Add the user to the role
        SELECT   @sql = REPLACE('USE [[@db_name]]; EXEC sp_addrolemember ''db_datareader'', ''' + @user_name + ''';', '[@db_name]', @db_name)
    
        EXEC    (@sql)
    
        PRINT   @sql
    
    END
    Last edited by pootle flump; 11-26-09 at 13:17.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by pootle flump View Post
    • Even though I was logged in using a SQL Server login, the wizard defaulted to authenticating using windows authentication - I had to re-enter my SQL Server log in details ****I bet this is where your process is going wrong - do these people have access to the database via windows authentication?****
    You are correct - The Database defaults to Windows on my machine and works because I already have access to the server.

    Changing to SSA fails the Import.

    I'll have to check to see if they can access the server through remote desktop.

    Thanks for your help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •