What should be granted?

Question

Hi,
What privileges should be granted to ws_login to resolve this?
use [my_schema]
go

select *
from user_mst
go

Msg 916, Level 14, State 1, Line 1
The server principal "ws_login" is not able to access the database "my_schema" under the current security context.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'user_mst'.

---

Many Thanks & Best Regards, Hua Min

Answer 1

http://blogs.technet.com/b/mdegre/archive/2010/08/28/the-server-principal-quot-sqlloginname-quot-is-not-able-to-access-the-database-quot-mydatabasename-quot-under-the-current-security-context.aspx

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

Thanks. When I run this
USE [my_schema]
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='ws_login',@LoginName=' ws_login ';

I've got
Msg 916, Level 14, State 1, Line 1
The server principal "ws_login" is not able to access the database "my_schema" under the current security context.

---

Many Thanks & Best Regards, Hua Min

Answer 3

Hello HuaMin Chen,

You have to add the server login as database user and you have to grant select permission for that db user to the table.

USE [my_schema]
GO

CREATE USER [ws_login] FOR LOGIN [ws_login]
GO

GRANT SELECT ON [dbo].[user_mst] TO [ws_login]
GO

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

Hi Hua !

You may need to look at the below link;

http://gunnalag.wordpress.com/2011/08/30/fix-the-server-principal-username-is-not-able-to-access-the-database-sql_db-under-the-current-security-context/

http://connect.microsoft.com/SQLServer/feedback/details/424329/the-server-principal-testuser-is-not-able-to-access-the-database-msdb-under-the-current-security-context

USE [my_schema]
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='ws_login',@LoginName=' ws_login '; 

You need to run those queries with sys admin user.

Please let me know if this doesn’t work for you. Hope I have answered you correctly.

Thanks, Hasham Niaz

Answer 5

Thanks to all.
Olaf,
I can login as ws_login. Now I have logged in as "sa" and then do this.

USE my_schema;

grant select on dbo.user_mst to ws_login;
go

why is there a problem?

Msg 15151, Level 16, State 1, Line 3
Cannot find the user 'ws_login', because it does not exist or you do not have permission.

Hasham,
How to login as sys admin user?

---

Many Thanks & Best Regards, Hua Min

Answer 6

Msg 15151, Level 16, State 1, Line 3
Cannot find the user 'ws_login', because it does not exist or you do not have permission.

But only a server login is not enough, as I already wrote you have to add the "server login" as "database user", so that the login can access the database at all!

CREATE USER [ws_login] FOR LOGIN [ws_login]

login <> user, consider this please.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 7

As you logged in 'sa' then most often then not you should be having sysadmin privileges. Please run following t-sql with replace <Schema_Name> with your schema name & <Login_Name> with your login name.

GRANT SELECT ON SCHEMA ::<Schema_Name> TO <Login_Name>;

Hope that helps.