[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Limit Database Access to one database

Hi Experts,
This is for MS SQL2000

How Can I limit acces to <test> database and still have admin rights to create backups and table and modify table structures only that <test> database.

A consultant will be accessing database on database server using enterprise manager from client tools installed on terminal server.
I would like to just limit the access only to this <test> database. No other database should be accessible. They require administrative rights on this database.

Thank You
0
macentrap
Asked:
macentrap
  • 5
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You can create a login and map it to db_owner of only the <test> database. If you are unsure how to do this, please post back.
0
 
macentrapAuthor Commented:
Thank you, this is access I have already done, wanted to make sure.


Today IT admin will create an AD account for consultant, will test it further then.
0
 
macentrapAuthor Commented:
posted to quick,

Well when I try to register SQL server wizard using this account,

Connect using radio button are missing can only see the text

though going next provides only option as sql user

Would you knw, is it because the terminal server is on workgroup not domain?

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Kevin CrossChief Technology OfficerCommented:
That should work just fine. As long as your AD accounts have access to connect to the Database Engine the user being granted the db_owner role on only that database should do what you need. If you sense otherwise, you can always explicitly deny rights on the other databases.
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, I did not see your last post until now...yes, that is my understanding. If your actual SQL server is in the domain, you can go there to setup the user. Just note that using Windows authentication, SSMS defaults to the logged in credentials. You can use runas to start SSMS using the AD credentials if the terminal server as long as the terminal server can communicate with domain. This may be very cumbersome unless you create a shortcut/batch file that always starts SSMS with runas, prompting the consultant for password. Guess you could use a sql user, but that may violate your admin policies.
0
 
macentrapAuthor Commented:
Thank you, will update soon !
0
 
macentrapAuthor Commented:
after registering using SQL authentication, I can see list of database no access which is good

is it possible only <test> database is visible.
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry for the delay. Just catching up on all e-mails. You can execute the following from the master database.
USE master;
GRANT VIEW ANY DATABASE TO <login name>;
GO

Open in new window


That works. Now, it almost works too well sometimes. Just make sure that the login is definitely the dbo.
USE <database name>;
EXEC sp_changedbowner '<login name>'
GO

Open in new window

0
 
macentrapAuthor Commented:
Thank you, worked good.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now