DB Access is not working

  • Hi,

    I tried to give one of the user as datareader access for DB but the user is not able to view the table definition until he get db_owner access. I tried some other access also but none of the access is working for him except db_owner.

    Can anyone help me on this because this is happening in one DB only , for other DB there is no issue for any user.

    Thanks,

    Arooj

  • Try

    GRANT VIEW DEFINITION

    https://technet.microsoft.com/en-us/library/ms175808(v=sql.105).aspx

  • to view the table structure, you want to GRANT VIEW DEFINITION TO SomeUser instead of db_owner

    that effectively allows them to see sys.tables/sys.columns, etc...all the metadata.

    it lets them see procedure and view definitions as well.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks for the reply both of you:)

    I tried Grant view definition also but still its not working.:(

  • what does "still not working" mean?

    is the end user using SSMS? queries? is there a specific error message?

    rereading your original post, you said it's happening in one database only, so it's quite possible someone explicitly did a deny view definition to public to prevent exactly what you are trying to do....maybe that's the issue?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • when user is tried to access table that is not visible for him, only system table is displaying for him

  • Hi All,

    with the help of below script his name is showing in other DB but not in this DB

    SELECT a.*,b.name as 'Login ID' FROM sys.database_permissions as A

    inner join sys.server_principals as B on a.grantee_principal_id = b.principal_id

    where b.name = 'omh\srinathp'

    Hope this will help to understand my issue.

  • each database is seperate, so you need to explicitly add thelogin as a user to the other database

    IF NOT EXISTS(SELECT * FROM sys.server_principals where type_desc='WINDOWS_LOGIN' AND name = 'omh\srinathp' )

    CREATE LOGIN [omh\srinathp] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

    IF NOT EXISTS (select * from sys.database_principals where name = 'omh\srinathp')

    BEGIN

    CREATE USER [omh\srinathp] FOR LOGIN [omh\srinathp];

    END

    GRANT VIEW DEFINITION TO [omh\srinathp]

    EXEC sp_addrolemember 'db_datareader','omh\srinathp';

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi Lowell

    Thanks for you valuable time. but still that user is not able to access any tables from the DB. I think the problem is in DB Structure only , because for SQL authentication db_datareader is working but for Windows Authentication having problem.

    I am not able to find what is the issue.

    Thanks ,

    Arooj

  • I don't know enough about the issue you're having, but I thought I'd share a brief walk-through (as best I can recall) of a recent successful setup of a SQL Server on a local server and users accessing through Microsoft Access on workstations in the same office, just in case you might see a step in here and think, "oh, I didn't do that!" 🙂

    (BTW, I'm basing this on the assumption that the user is trying to see the tables in Microsoft Access, because the subject line implied that. Most of these instructions apply without that, though... at least up until the "ODBC" step.)

    - Installed the SQL Server on the server. Can't recall exactly what services I set it up under, unfortunately, and I can't check right now. It most likely matters, though, so you may want to check that, if you haven't already. I set it up with Windows Level Authentication.

    - Restored the database (from development) to the server.

    - In SSMS, went into Logins on the server level (under Security), and added the logins (in the case of my database, we wanted all legitimate domain users to have read access, so I set up a login for BUILTIN/USERS, as well as individual logins for people who needed more specialized access.)

    - Then I went into the database and set up the Users there as well (under Security), making sure that each User was connected to a Login. This is where I added them to the roles that they belonged to.

    - At this point, if a user had sat down at the server machine, and logged in as him/herself, then went into SSMS, they would have been able to see the tables. But nobody that's not physically at that machine would be able to see the SQL Server at all. So I configured the Windows firewall to allow traffic on a specific port according to the instructions here: https://msdn.microsoft.com/en-us/library/cc646023.aspx#BKMK_dynamic_ports and here: https://msdn.microsoft.com/en-us/library/ms177440.aspx. I've learned through painful experience that dynamic ports and SQL Browser are evil; things seem to work much better when you configure the server to use a specific port. That's just been my experience.

    - On each workstation, I set up ODBC connections that are configured to connect to the specific port that I set up in the SQL Configuration Manager (see previous step) and the firewall. Also made sure the default database was set to the right database in the ODBC connection... as opposed to being set to connect to "master," which is the default setup. That might be the problem right there, if they're only seeing system tables... Oh, and be sure you're using the right version of the ODBC connector. Usually, the right version is the 32-bit version. If after setting it up, you're able to see it as an option when using the Linked Table Manager in Access, then you're using the right version. Also, I did all this while logged into the workstation as the domain administrator (not sure if that makes a difference).

    That's it. At that point they were able to connect to the database. That's the best I can recall. Hope it helps you at all. 🙂

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

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