• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Sql Server Login rights


I  have third party vendor implementing an application which requires access to his sql db (DMR) hosted on our internal server. The server also hosts other DB of different applications.  ther server (sql2005) works on Windows authentication only.  I have created  a AD user "SCAN1" and  mapped  it to the DMR. But when i open SQL Mgmt studio and connec to the server, i could see all the components of the server.  Is it possible to give access to only this particular DB and nothing else. If i could make only this DB visible and rest of it hidden , that would be best. the application wants sysadmin role on this particular DB. Pls help
0
venkataramanaiahsr
Asked:
venkataramanaiahsr
  • 2
  • 2
  • 2
  • +2
1 Solution
 
skaraiCommented:
Most likely you have overssigned permissions to the SCAN1 account. Did you put the SCAN1 account in the local Administrators group on the server? SCAN1 should map only to the respective vendor DB inside SQL server - preferably mapped to the dbo schema in that db. On your SQL server check whether Builtin\Administrators are there and still have full system admininstraror privileges inside SQL server which you could lower to public only as long as you have appropriate permission settings for needed admin accoounts.
0
 
MrAliCommented:
Here's what you should do:

-Open up SQL Server Management Studio - The Server - Security - Right click on Security - New Login - Create Windows user there with the SERVER ROLES setting set to 'public'.  Or you can run this script:


USE [master]
GO
CREATE LOGIN [DOMAIN OR SERVERNAME\USERNAME] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO


Then you want to add that user as a DBO to the application.  Open up the DATABASE, right click on SECURITY under the database, go to "NEW LOGIN".  Under Login Name hit the 3 dots and select your user name you just created. Under Username, copy paste the login name into that. Under "ROLE MEMBERS" (The bottom half) select "DB_Owner" and hit OK.  You're good to go!  You can also just run this script:




USE [DATABASENAME]
GO
CREATE USER [DOMAIN OR SEVERNAME\USERNAME] FOR LOGIN [DOMAIN OR SEVERNAME\USERNAME]
GO
USE [DATABASENAME]
GO
EXEC sp_addrolemember N'db_owner', N'DOMAIN OR SEVERNAME\USERNAME'
GO


If you don't know the username, log in with that user into windows, open up a command prompt and type in: whoami and push enter.  Copy the result.  Do a CTRL+H in SQL to do a 'find and replace'.  Replace DOMAIN OR SEVERNAME\USERNAME with the result.  Replace DATABASENAME with the database name.  Good luck!
0
 
venkataramanaiahsrAuthor Commented:

Scan1 is just a domain user a/c .  It has no other privilages.  I have a created a login domainname\scan1 and mapped only dmr db to this login  and default db as dmr. only public/db_owner role is assigned.  

When i login from a client m/c using  domainname\scan1  and open sqlmgmet studio and connect to the server, all the db and other mgmet parts like security etc are visible to the user.  Of course, it says access denied when i try to open any other db. is it not possible to hide all other parts except dmr in the studio for this particular login.
 i,e user should be able to do  select/insert/update/delete operations on the DMR db only and nothing else.

Currently with the above setttings,  i am able to  select operations on linked server.  i am able to change the default db for the scan1 login.  I am able to access some of the tables of system db.

In nutshell, what i want is for this login, i should have  complete rights on dmr db and nothing on other db/system managment operations.


0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
GSGDBACommented:
Venkat,
You assigned correct privileges to scan1. :)
To your queries.
1) Hide other database visibility to scan1: When SCAN1 use SSMS, Scan1 could see other databases.
But couldn't access those databases. And Scan1 can select results from system databases, linked servers too.

2) When the user connects using application. User will not be able to see the dmr and other database names. Its only when they developer or vendor connects SQL server Using SSMS.



0
 
GSGDBACommented:
Make sure no roles is selected in Server roles tab of Scan1 Properties.
So that linked servers access is not possible to scan1.
go to security -> logins -> scan1-> right click -> Properties -> Select Server roles pane.
0
 
TempDBACommented:
yeah and as skarai said remove the permissions for the user. Looks like you assigned the login as sysadmin.
Or else delete the login, create a new one as MrAli suggested.
0
 
venkataramanaiahsrAuthor Commented:
I want to give sysadminrole for scan1 to access only DMR DB. this id should have complete acess to this DB and nothing to other DB or any other part of sql server tools in the sql management studio. Currently i cannot access any DB but still some portion of mgmt studio is accessible to this userid (like linked servers etc).

My next point is it possible to hide all the other db in mgmt studio except DMRDB
0
 
skaraiCommented:
SQL server security is a layered model of server role and db role or access privileges.
Under server roles the scan1 account should only have public which is the minimum SQL server role privilege to connect to SQL server - on the database level the login should only map to the db_owner role which allows the login to perform any operation inside that db.The default login db for scan1 should be DMR - not master
Hiding objects in SMS is not an option.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now