March 10, 2015 at 4:59 am
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
March 10, 2015 at 5:22 am
March 10, 2015 at 5:24 am
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
March 10, 2015 at 5:33 am
Thanks for the reply both of you:)
I tried Grant view definition also but still its not working.:(
March 10, 2015 at 5:42 am
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
March 10, 2015 at 5:45 am
when user is tried to access table that is not visible for him, only system table is displaying for him
March 10, 2015 at 6:40 am
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.
March 10, 2015 at 6:47 am
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
March 10, 2015 at 8:02 am
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
March 17, 2015 at 9:52 pm
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