1

I have access to any database on this server using my ID and password. Here's my connection string:

Data Source=my.database; Initial Catalog=main_table; User ID=my_user_id; Password=my_password;

It works, but now I want to deny access for my_user_id. The problem is - I cannot find the user anywhere in the DB.

How is it even possible? It looks like an invisible account. It doesn't list anywhere, however SQL grants privileges to that account.

I'm lost, where should I look for this account?

  • Access is given to users or groups. Is your user an admin? By default, admins have full access. Or maybe your login is part of another group that has access. – BlueGI Oct 25 '18 at 13:52
  • Or check your SQL login Server Roles for sysadmin or similar. – BlueGI Oct 25 '18 at 13:59
  • Oops, it looks like the account was indeed hidden from the user connected to the SQL server. I thought, the fact I connect as a Windows user in Administrators group implies the user is automatically the SQL server administrator. Well, not exactly. As said user I can create and drop tables, but not even BROWSE some users accounts. It's weird. I would understand if my privileges was too low to change the user's password, but not to just list users. I saw user sa but not the_user. Weird and unintuitive. And now awkward ;) – Harry Oct 26 '18 at 11:09
0
SELECT CASE 
        WHEN sysadmin = 1
            THEN 'You have system admin access'
        ELSE 'Normal user'
        END
FROM sys.syslogins
WHERE loginname LIKE '<loginname>'

the above query will return 'You have system admin access' means that the the provided login has sysadmin privilege else the provided login doesn't have sysadmin privilege.

| improve this answer | |
  • This answer needs an explanation. – kasperd Oct 26 '18 at 13:31
0

Solution:

Server login must be associated to a database user to perform actions in the database. Both are principals in SQL Server. In a connection string, value for user id is the name of the server login. You may try to check the user mappings for this server login for your current database:

USE main_table               -- Server database

SELECT 
    sp.name AS LoginName,
    sp.type_desc AS LoginType,
    dp.name AS DatabaseUserName,
    r.name AS DatabaseRoleName
FROM sys.server_principals sp
LEFT JOIN sys.database_principals dp ON dp.sid = sp.sid
LEFT JOIN sys.database_role_members drm ON drm.member_principal_id = dp.principal_id
LEFT JOIN sys.database_principals r ON r.principal_id = drm.role_principal_id
WHERE sp.name = 'my_user_id' -- Server Login

Notes:

With your current connection string, I think that you try to connect to SQL Server with SQL authentication. Then your SQL login is mapped to a database user (server login is not equal to database user). If you want to connect using Windows authentication, in most cases is necessery to remove User ID and Password from connection string and include IntegratedSecurity=true.

| improve this answer | |
  • The connection string is the one the application used. What I tried to achieve was to temporarily deny the application the access. From the MSSMS level, being logged as Windows user. I couldn't edit the connection string accordingly. When I'm logged as Windows user, I don't see SQL logins at all. Then when I logged as the user I tried to disable - I saw ALL users and I was able to change my own password. Still, I don't quite understand what has happened. – Harry Oct 26 '18 at 18:52
  • @Harry What does the script in the example return? Thanks. – Zhorov Oct 26 '18 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.