September 23, 2011 at 9:52 am
We have several customers for whom we will soon begin allowing read access directly to our databases. When they connect via Sql Server Visual Management Studio to our database engine, we'd like them to only see the databases listed on the left that they've got permissions to access. Is there a way to do this without creating a named instance for each customer?
thanks in advance for any ideas you might propose.
seth
September 23, 2011 at 9:55 am
It's the default behavior. If they can't access it they shoudln't be able to see it.
If they do you probably messed up something.
September 23, 2011 at 9:55 am
On the other hand.
Any reason you're not doing some sort of replication to a 2nd server so they can't mess with production?
September 23, 2011 at 10:15 am
These customers need read access to the production database and we don't have the hardware or software licenses available to create a replication environment.
The default behavior for a login created in the database engine is that it can see all the databases on the server even though it is mapped for read permission to only a single database.
seth
September 23, 2011 at 11:31 am
the command you are looking for is this snippet to hide databases the end user doesn't have access to:
DENY VIEW ANY DATABASE TO [SomeUserOrRole]
here's some testable code to prove it.
IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'DbViewerTesting')
BEGIN
EXEC MASTER.dbo.sp_addlogin @loginame = N'DbViewerTesting', @passwd = 'NotARealPassword', @defdb = N'master', @deflanguage = N'us_english'
--add this user to permit read and write
END
EXECUTE AS LOGIN='DbViewerTesting'
USE master
GO
select * from master.sys.databases -- see them all!
REVERT;--turn back into SuperMan
DENY VIEW ANY DATABASE TO DBVIEWERTESTING
EXECUTE AS LOGIN='DbViewerTesting'
USE master
GO
select * from master.sys.databases -- see master and temp only!
REVERT;--turn back into SuperMan
DROP LOGIN DbViewerTesting
Lowell
September 23, 2011 at 12:13 pm
Lowell (9/23/2011)
DENY VIEW ANY DATABASE TO [SomeUserOrRole]
[/QUOTE]
Thank you very much for this example! I wasn't aware of the view any database privilege. I looked up the microsoft docs on it, and it looks like that privilege isn't very granular. It says the user who has been denied 'view any database' will only see the databases to which the user is an owner. I added my customer's login as an owner of the appropriate database, but with the deny view any database applied, the user doesn't "see" any of the databases in the object explorer. If the user does a "use databaseXYZ", then the query window switches to the appropriate database, but this isn't especially intuitive. I've got to make this as simple as possible for this breed of user....
ideas?
-- mtf
September 23, 2011 at 12:28 pm
Have the default database login for the user changed to their database? Unless they set it in their connection setting.
September 23, 2011 at 12:54 pm
Jo,
That's a good suggestion, and I have done that, but in this scenario, each customer will have multiple databases.
From the research I've done, this looks to be a limitation on SQL Server 2008. I guess I'll have to suffer the unintuitiveness of them not being able to see their own databases within the object explorer.
Thanks to everyone for their suggestions!
-- Seth Johnson
September 23, 2011 at 1:36 pm
Seth when i change the database owner, i seem to see exactly what id'd expect...two more databases
using my same code from above:
DENY VIEW ANY DATABASE TO DBVIEWERTESTING
USE SandBox
GO
exec sp_changedbowner DbViewerTesting
USE PERFECT1000
GO
exec sp_changedbowner DbViewerTesting
EXECUTE AS LOGIN='DbViewerTesting'
USE master
GO
select * from master.sys.databases -- see master,temp,SandBox and PERFECT1000 only!
REVERT;--turn back into SuperMan
DROP LOGIN DbViewerTesting
Lowell
September 23, 2011 at 1:53 pm
Lowell,
I can't grant dbowner role to these users or else then they'd be able to write to the db or make other changes. I've got these users tied down to datareader role... Unless you know of a way to restrict a dbowner from making changes, I don't think that will work.
-- seth
September 29, 2011 at 11:13 am
Lowell,
I received permission from the organization here to allow the customer user to be dbowner. I thought I'd try the method you suggested in this snippet, but I receive an error. What permissions do I need to change on the DB to enable this 'DbViewerTesting' user to see the DB?
Lowell (9/23/2011)
here's some testable code to prove it.
IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'DbViewerTesting')
BEGIN
EXEC MASTER.dbo.sp_addlogin @loginame = N'DbViewerTesting', @passwd = 'NotARealPassword', @defdb = N'master', @deflanguage = N'us_english'
--add this user to permit read and write
END
EXECUTE AS LOGIN='DbViewerTesting'
USE master
GO
select * from master.sys.databases -- see them all!
Here is the error message this part of your code generates on my system:
Msg 916, Level 14, State 1, Line 1
The server principal "DbViewerTesting" is not able to access the database "<the database name>" under the current security context.
(5 row(s) affected)
thanks in advance for any ideas,
mtf
September 29, 2011 at 11:26 am
mrTexasFreedom (9/29/2011)
Lowell,I received permission from the organization here to allow the customer user to be dbowner. I thought I'd try the method you suggested in this snippet, but I receive an error. What permissions do I need to change on the DB to enable this 'DbViewerTesting' user to see the DB?
Here is the error message this part of your code generates on my system:
Msg 916, Level 14, State 1, Line 1The server principal "DbViewerTesting"
is not able to access the database
"<the database name>"
under the current security context.
(5 row(s) affected)
thanks in advance for any ideas,
mtf
ok, looks like you've done the DENY VIEW so that's just one peice;
somehwere you needed to run this to make them the owner:
USE "<the database name>"
GO
exec sp_changedbowner 'DbViewerTesting'
once that is done, you should see they can see their database.
Lowell
September 29, 2011 at 12:04 pm
Lowell,
When I execute the syntax you suggested, it seems to never complete. Should it really take a long time for this stored proc to finish?
USE <database name>
GO
exec sp_changedbowner 'DbViewerTesting'
mtf
September 29, 2011 at 2:15 pm
you need exclusive access to the database in order to change the owner...so if someone is in it, the query waits for them.
you'll need to wait till someones not in it, or kick everyone out for the two seconds it takes to run the command and let them back in...might not be allwoed to do that on a production system.
Lowell
September 29, 2011 at 3:30 pm
Lowell (9/29/2011)
you need exclusive access to the database in order to change the owner...
So the goal here is to actually change the owner rather than add the user to the dbowner user mapping for the database? That must be what you're suggesting, because when I add dbowner mapping to this user in the login properties, the user still can't see the database in the object explorer after connecting.
I'll verify this with a non-production DB and get back to you on the results shortly.
Appreciatively,
mtf
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply