Understanding Logins Versus Users

Kenneth Fisher has a user which should have rights but is unable to access the database in question:

The other day I ran across an interesting problem. A user was logging in but didn’t have access to a database they were certain they used to access to. We checked and there they were. Not only was there a database principal (a user) but it was a member of db_owner. But still no go. The user could not connect. I went to the database and impersonated them and then checked sys.fn_my_permissions. They were definitely a member of db_owner. I tested and yes, I could read the tables they needed, and yes they could execute the stored procedures they needed to execute. So what was wrong?

Keep those principals in alignment.

Related Posts

SQL Server and Recent Security Patches

Allan Hirt takes us through recent security updates and how they pertain to SQL Server: After Spectre and Meltdown a few months back (which I cover in this blog post from January 4), another round of processor issues has hit the chipmaker. This one is for MDS (also known as a ZombieLoad) This one comprises […]

Read More

NT AUTHORITY\ANONYMOUS Error Editing Procedures

Kenneth Fisher takes us through a security issue: If you have to deal with linked servers then you probably have or will run into the following error: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ But I’m not trying to use the linked server. I’m trying to create/alter a stored procedure. Kenneth explains why you might […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29