Post reply

Create sql login for reporting activity

  • March 3, 2017 at 6:47 am

    #335699

    Hello,
    I need help to accomplish a 'simple' task.
    Our customer need to connect to our sql server (one server for all client) via SSMS and with SQL authentication, browse only their database and make reporting activity (read only activity).

    My actual solution is
    - create a public Server login [Customer_X_Report] and map as db_datareader in the target database.
    -DENY VIEW ANY DATABASE TO [Customer_X_Report]

    In this way, the customer connects via SSMS with [Customer_X_Report] can only see Master and tempdb (so difficult to make reporting activity...)

    Thanks in advance for your help
    'A noob DBA'

  • March 3, 2017 at 7:09 am

    #1931493

    PeopleFlyFree - Friday, March 3, 2017 6:47 AM

    Hello,
    I need help to accomplish a 'simple' task.
    Our customer need to connect to our sql server (one server for all client) via SSMS and with SQL authentication, browse only their database and make reporting activity (read only activity).

    My actual solution is
    - create a public Server login [Customer_X_Report] and map as db_datareader in the target database.
    -DENY VIEW ANY DATABASE TO [Customer_X_Report]

    In this way, the customer connects via SSMS with [Customer_X_Report] can only see Master and tempdb (so difficult to make reporting activity...)

    Thanks in advance for your help
    'A noob DBA'

    I think that GRANT can answer your question
    😎

  • March 3, 2017 at 8:11 am

    #1931513

    Hello,
    thanks for your reply, but I'm still not find the correct solution.

    I try with this script below, but if i'm connect to the server (SQL  Server 12.0.5000.0) via SSMS with the C1_reporting login I see only Master and tempdb:

    CREATE DATABASE [Customer_1]
    GO

    USE [Customer_1]
    CREATE TABLE Data1 (Col1 INT, Col2 nvarchar(5))
    GO

    USE [master]
    GO
    CREATE LOGIN [C1_reporting] WITH PASSWORD=N'C1_reporting', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [Customer_1]
    GO
    CREATE USER [C1_reporting] FOR LOGIN [C1_reporting]
    GO
    USE [Customer_1]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [C1_reporting]
    GO
    use [master]
    GO
    DENY VIEW ANY DATABASE TO [C1_reporting]
    GO

    use [Customer_1]
    GO
    GRANT VIEW DEFINITION TO [C1_reporting]
    GO

  • March 3, 2017 at 8:37 am

    #1931528

    I know there have been complaints about this in the past and I thought the issue was that access to a database is stored in the database itself. Database owner is stored in master so maybe if the customer login is the owner of the database? I think that used to be the only workaround.

    Sue

  • March 3, 2017 at 9:38 am

    #1931560

    I know there have been complaints about this in the past and I thought the issue was that access to a database is stored in the database itself. Database owner is stored in master so maybe if the customer login is the owner of the database? I think that used to be the only workaround. 

    Sue

    I saw many post with this solution/workaround but I can't reproduce it. The customer login db_owner only see Master and tempdb.
    Can you please create a script for this solution?

    Thanks in advance

  • March 3, 2017 at 11:20 am

    #1931588

    PeopleFlyFree - Friday, March 3, 2017 9:38 AM

    I know there have been complaints about this in the past and I thought the issue was that access to a database is stored in the database itself. Database owner is stored in master so maybe if the customer login is the owner of the database? I think that used to be the only workaround. 

    Sue

    I saw many post with this solution/workaround but I can't reproduce it. The customer login db_owner only see Master and tempdb.
    Can you please create a script for this solution?

    Thanks in advance

    What aspect of this isn't working the way you need?

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • March 3, 2017 at 12:20 pm

    #1931607

    PeopleFlyFree - Friday, March 3, 2017 9:38 AM

    I know there have been complaints about this in the past and I thought the issue was that access to a database is stored in the database itself. Database owner is stored in master so maybe if the customer login is the owner of the database? I think that used to be the only workaround. 

    Sue

    I saw many post with this solution/workaround but I can't reproduce it. The customer login db_owner only see Master and tempdb.
    Can you please create a script for this solution?

    Thanks in advance

    Not sure what isn't working. I can only guess you had some errors in trying this somewhere along the lines.
    But on a SQL Server 2014 instance:

    CREATE DATABASE SomeDatabase
    GO

    USE SomeDatabase
    CREATE TABLE Data1 (Col1 INT, Col2 nvarchar(5))
    GO

    USE master
    GO
    CREATE LOGIN SomeUser WITH PASSWORD=N'SomeUser',
    DEFAULT_DATABASE=master,
    CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO

    DENY VIEW ANY DATABASE TO SomeUser;
    GO

    ALTER AUTHORIZATION ON DATABASE::SomeDatabase TO SomeUser;
    GO

    SomeUser just sees SomeDatabase, master and tempdb.

    Sue

  • March 5, 2017 at 1:41 pm

    #1931717

    PeopleFlyFree - Friday, March 3, 2017 6:47 AM

    Hello,
    I need help to accomplish a 'simple' task.
    Our customer need to connect to our sql server (one server for all client) via SSMS and with SQL authentication, browse only their database and make reporting activity (read only activity).

    My actual solution is
    - create a public Server login [Customer_X_Report] and map as db_datareader in the target database.
    -DENY VIEW ANY DATABASE TO [Customer_X_Report]

    In this way, the customer connects via SSMS with [Customer_X_Report] can only see Master and tempdb (so difficult to make reporting activity...)

    Thanks in advance for your help
    'A noob DBA'

    There is no way in hell that I'd allow this.  You can't think just of the data access.  You have to think about resource usage, as well.  Reporting is frequently the highest resource usage type of query that anyone can write and users abuse the hell out of that.  They'll frequently use their reporting privs to build a query that will create a report of EVERYTHING and then download all of that so that they can "play" with it all on their end.  While all of that is happening (especially if they make accidental many-to-many joins where they use DISTINCT to get rid of duplicates), the CPUs are screaming, memory is filling up, an I/O has gone through the roof, all of which will paralyze the server for any other work being done even if it's not in the same database.

    The best thing to do is ask them for what they want to see for a report and send it to them.  Of course, if any PII is involved, it must be through secure channels.  So would it be needed for a direct connection on their part.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 6, 2017 at 7:31 am

    #1931788

    Jeff Moden - Sunday, March 5, 2017 1:41 PM

    PeopleFlyFree - Friday, March 3, 2017 6:47 AM

    Hello,
    I need help to accomplish a 'simple' task.
    Our customer need to connect to our sql server (one server for all client) via SSMS and with SQL authentication, browse only their database and make reporting activity (read only activity).

    My actual solution is
    - create a public Server login [Customer_X_Report] and map as db_datareader in the target database.
    -DENY VIEW ANY DATABASE TO [Customer_X_Report]

    In this way, the customer connects via SSMS with [Customer_X_Report] can only see Master and tempdb (so difficult to make reporting activity...)

    Thanks in advance for your help
    'A noob DBA'

    There is no way in hell that I'd allow this.  You can't think just of the data access.  You have to think about resource usage, as well.  Reporting is frequently the highest resource usage type of query that anyone can write and users abuse the hell out of that.  They'll frequently use their reporting privs to build a query that will create a report of EVERYTHING and then download all of that so that they can "play" with it all on their end.  While all of that is happening (especially if they make accidental many-to-many joins where they use DISTINCT to get rid of duplicates), the CPUs are screaming, memory is filling up, an I/O has gone through the roof, all of which will paralyze the server for any other work being done even if it's not in the same database.

    The best thing to do is ask them for what they want to see for a report and send it to them.  Of course, if any PII is involved, it must be through secure channels.  So would it be needed for a direct connection on their part.

    Hey, old man, get out of the way of our developers!  :rolleyes:

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • March 6, 2017 at 7:56 am

    #1931808

    Eric M Russell - Monday, March 6, 2017 7:31 AM

    Jeff Moden - Sunday, March 5, 2017 1:41 PM

    PeopleFlyFree - Friday, March 3, 2017 6:47 AM

    Hello,
    I need help to accomplish a 'simple' task.
    Our customer need to connect to our sql server (one server for all client) via SSMS and with SQL authentication, browse only their database and make reporting activity (read only activity).

    My actual solution is
    - create a public Server login [Customer_X_Report] and map as db_datareader in the target database.
    -DENY VIEW ANY DATABASE TO [Customer_X_Report]

    In this way, the customer connects via SSMS with [Customer_X_Report] can only see Master and tempdb (so difficult to make reporting activity...)

    Thanks in advance for your help
    'A noob DBA'

    There is no way in hell that I'd allow this.  You can't think just of the data access.  You have to think about resource usage, as well.  Reporting is frequently the highest resource usage type of query that anyone can write and users abuse the hell out of that.  They'll frequently use their reporting privs to build a query that will create a report of EVERYTHING and then download all of that so that they can "play" with it all on their end.  While all of that is happening (especially if they make accidental many-to-many joins where they use DISTINCT to get rid of duplicates), the CPUs are screaming, memory is filling up, an I/O has gone through the roof, all of which will paralyze the server for any other work being done even if it's not in the same database.

    The best thing to do is ask them for what they want to see for a report and send it to them.  Of course, if any PII is involved, it must be through secure channels.  So would it be needed for a direct connection on their part.

    Hey, old man, get out of the way of our developers!  :rolleyes:

    BWAAA-HAAA!  If they stop and think about it, I'm giving them the opportunity to shine. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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