Database Security (Logins with specific DB access)

Question

I am somewhat new to SQL Server 2008 R2 Express. 

Here is my issue.

I Have a Database (MyCompanyDATA) and I want to have SQL Server to ONLY have 2 logins.

1. That my my software will interact with the database so I want FULL admin rights with that account. 
2. A second account that can Log into SQL server but not be able to view the Table Structure. 

ALL OTHER ACCOUNTS I WOULD LIKE TO REMOVE OR DISABLE.

I would also like to have my login account only able to view MyCompanyDATA, nothing else.

I cant seem to acheive this....can someone point me in the correct direction?

Thank you in advance.

Answer 1

• Create Login & set default database "MyCompanyDATA"
• Give only Public rights as Server Role
• Now give Map login with database "MyCompanyDATA" & create user in database
• As you mention you need admin rights on database, give DB_OWNER rights to user

test out the thigns, it should work

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 2

If I ensure I have atleast 1 Login belonging to the Server Role (sysadmin) can I delete the remaining logins?

Here are the Logins that are in currenty in my Folder, all were created automatically, I only created my 1 login account:

##MS_PolicyEventProcessingLogin##
##MS_PolicyTsqlExecutionLogin##
MyMainLogin......................This is the only one I NEED
BUILTIN\Users
NT SERVICE\MSSQL$[MyInstanace]
sa
Work5-PC\Work5 ................This one looks like it is my Local Windows Account

Can I remove them all but the one I need so long as it is belonging to the sysadmin role?
If not which ones should I leave?

Thanks.

Answer 3

You should not delete those starting with hash marks, as they are added by SQL Server setup for internal use. It is more likely that you will get problems if you drop them, and if you don't.

You cannot drop sa, but you can rename it and disable it.

You can drop your own login, but you need one acount that can be sysadmin, either sa or your own account. I recommend the latter.

You can drop BUILTIN\Users - this group ensures access to all Windows logins on the machine.

The NT SERVICE thing is the service account. I recommend that you keep it. At some point you may want a loopback connection.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Ok I have disabled the sa account. I have made my 2 logings. My MAIN one which is a member of role public and sysadmin.

I also created a second account callled User which a member of only public.  I am unable to login into sql server with just public role checked....The second Login has nothing checked under User Mappings, and the default db is 'master', is that correct? I dont quite know where I went wrong.

Answer 5

1) You should be able to connect to SQL server with public role. It seems your SQL server is runing on WINDOW AUTHENTICATION mode, change it to MIXED mode & restart the services. Try after that.

2) After login creation

• set default database "MyCompanyDATA"
• Now give Map login with database "MyCompanyDATA" & create user in database
• As you mention you need admin rights on database, give DB_OWNER rights to user

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 6

I just rechecked. I am in Mixed Mode.

My first login I created the one that had sysadmin and public, works as I expected.

My second "user" login only has public checked and I am unable to login.SQL Server says:

TITLE: Connect to Server
------------------------------

Cannot connect to WORK5-PC\IINSTANCE.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456)

------------------------------
BUTTONS:

OK
------------------------------

Answer 7

Can you please check the SQL server error log for login failure error details & sahre the same for more details ?

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 8

where might I find the error log?

Answer 9

Run command & find failed login faced by by corresponding to the time given

Exec SP_readerrorlog

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 10

Login failed for user 'MyUser'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

Answer 11

Please share 3-4 line above & below this error.

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 12

Everthing above this is from earier in the afternoon. These are t

Server resumed execution after being idle 3656 seconds: user activity awakened the server. This is an informational message only. No user action is required.he last 5 records in the log.

Error: 18456, Severity: 14, State: 12.

Login failed for user 'MyUser'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.

Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_readerrorlog'. This is an informational message only; no user action is required. 

Answer 13

Run below commands with user having syadmin or security  permissions over SQL server. After runing these commands, try to connect with your user.

GRANT CONNECT SQL TO [MyUser]
GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO [MyUser]

Other solution (not recommended) :

Check UAC on client machine, if found on then turn off UAC (not recommended) or launch the application using the “Run as administrator” option.

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 14

I will give that a try in a little bit. I have a few other things I must do prior to this....family stuff.

thanks I will post my results.

Answer 15

Sorry still didnt work. Same error as before. I am not fully vesting in SQL yet so I can always uninstall and redo it hoping it would corect any faulty default login properties.  Then see if I get the errors.  Though Id like not to do if I can avoid it.

I do not have UAC enabled. I turned that horrible windows feature off 5 minutes after I received my new desktop.

This was the error. The other lines were the same as well. I even did a restart of SQL bfore I tried. but to no avail.

Login failed for user 'MyUser'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

Let me know if you have any other ideas, otherwise Monday I will uninstall SS2008 and reinstall it. Is there anything special I should delete if I am going to go this route?

Answer 16

Ok I uninstalled and reinstalled and everything seems to be working as you mentioned, didnt have to do anything special.

If I want my Login to have full access to my mapped databases what roles should I have checked? or is Public good enough?

Answer 17

server role - public will be fine

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 18

For full access in database, you need to be in the db_owner role.

---

Erland Sommarskog, SQL Server MVP, [e-mail]