I create a new user in MS SQL via MS SQL server managment in / SECURITY / LOGINS part.
User is set and can connect , however dispite the fact he see all databases he can get data just from one of them and from rest he can get just structure.
Is there some special settings to enable full access to specifc user to all DBs? Thanks
2 Answers 2
Unless the user is given the system adminstrator (sa) role, you will need to add a user to each database which is mapped to the login and which gives the correct permissions just for that database.
Be very careful creating users with global access to databases, it is the start of a slippery slope to a major hack!
As mentioned by Lukos you will need to give them access to each database individually under the "User Mapping" tab in Logins. I would do it this way and give them the least permissions that they require rather than just blanket giving them sysadmin.
One extra thing to note is if you give them the required access (datareader or owner etc...) to the model database any new databases that you create the user will automatically have the same permissions applied to the new databases.