January 26, 2023 at 11:44 am
I have a number of databases on a sql server 2017 instance.
For most databases i have been able to grant a user access to view the database tables in SSMS and run read queries (code below).
USE [master]
GO
CREATE LOGIN [myLogin] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [DatabaseA]
GO
CREATE USER [myLogin] FOR LOGIN [myLogin]
GO
USE [DatabaseA]
GO
ALTER ROLE [db_datareader] ADD MEMBER [myLogin]
GO
use master
go
GRANT VIEW ANY DEFINITION TO [myLogin]
I have two databases however where this is not working. The user cannot see the tables. they can run select queries.
I tried going into the user properties and granting view definition to each table in the database, but this also did not work.
These databases are compatibility level 130, in case its useful to know.
Im unsure what else to try here and would appreciate some expertise. thank you!
January 26, 2023 at 12:43 pm
For reference, there has already been some troubleshooting on this over on Stack Overflow, where there is some additional information in the comments.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 26, 2023 at 1:32 pm
did you try granting the account "view definition" on these specific databases ?
Using "any database" should indeed cover it all, but ...
use [yourdb]
create user ...
GRANT view definition TO [the account]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply