November 10, 2011 at 2:31 pm
Hello all-
I'm running an application that uses SQL Server 2008 R2 as the backend. What I need to do is allow 'User1' to be able to connect to a database named 'DB1' in SQL via the application, but not be able to view 'DB1' within SQL Server Management studio when logging in as 'User1'. I'd still like 'User1' to be able to see all other databases, and similarly, I'd like any user other than 'User1' to be able to see ALL of the databases, including 'DB1'.
How can I accomplish this?
🙂
November 10, 2011 at 2:37 pm
petemetz (11/10/2011)
Hello all-I'm running an application that uses SQL Server 2008 R2 as the backend. What I need to do is allow 'User1' to be able to connect to a database named 'DB1' in SQL via the application, but not be able to view 'DB1' within SQL Server Management studio when logging in as 'User1'. I'd still like 'User1' to be able to see all other databases, and similarly, I'd like any user other than 'User1' to be able to see ALL of the databases, including 'DB1'.
How can I accomplish this?
🙂
You should not use the same user. There is no reason that the database user of an application should be logging in to SSMS. If you want to prevent access to a database you do that at the user level. This probably means creating more than 1 user in your case.
Remember that SSMS is just an application that makes a database connection to sql server. It is not different in that respect than your front end. SSMS is just another front end with SQL behind it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2011 at 5:05 am
petemetz (11/10/2011)
Hello all-I'm running an application that uses SQL Server 2008 R2 as the backend. What I need to do is allow 'User1' to be able to connect to a database named 'DB1' in SQL via the application, but not be able to view 'DB1' within SQL Server Management studio when logging in as 'User1'. I'd still like 'User1' to be able to see all other databases, and similarly, I'd like any user other than 'User1' to be able to see ALL of the databases, including 'DB1'.
How can I accomplish this?
🙂
Once you have given the rights to User1, it will be able to view & access the DB1 from SSMS in any way. If you really want to restrict User1 from connecting to DB1 through SSMS, you can follow these steps:
1. Use User1 specifically for the application
2. Create a new login with access on all the other databases except DB1 & provide that to the other users
3. To keep an eye that nobody else connects to database server with User1 except the web application, you can create a LOGON TRIGGER which catches the IP Address (ClientHost) & LoginName of the caller. Based on the IP Address & LoginName you can decide what to do.
i.e.
IF (IP address NOT IN ( IP addresses of your web servers)) AND IF (LoginName = 'User1')
Rollback.
But, before implementing any of the above:
1. I would suggest you to completly go through from the LOGON TRIGGER to know very well about them.
http://technet.microsoft.com/en-us/library/bb326598.aspx
2. Test the above functionality again & again on the test serverto be sure that it meets your requirements & it is allowing the valid users to login in the database server from valid IPs.
November 25, 2011 at 5:58 am
REVOKE VIEW ANY DATABASE FROM user
I use
REVOKE VIEW ANY DATABASE FROM public to restrict all users seeing the databases in SSMS
Taggs
December 1, 2011 at 11:13 am
Thanks for the help everybody 🙂
December 1, 2011 at 11:31 am
Taggs (11/25/2011)
REVOKE VIEW ANY DATABASE FROM userI use
REVOKE VIEW ANY DATABASE FROM public to restrict all users seeing the databases in SSMS
Taggs
Just so long as folks are aware of the ramifications of doing this:
REVOKE VIEW ANY DATABASE FROM public;
Doing this means that users will not be able to see any databases other than master and tempdb. They can still get to them if they know they are there (by using USE dbname;), but they won't see them in the drop down. The only way you can get them to see the database in the dropdown is to make the owner of the database.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply