Sql Server Login rights

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.

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!

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.

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.

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.

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.

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