locked
Wrong mapping logins to database users RRS feed

  • Question

  • I have strange problem.

    After this script:

    USE [master]
    GO
    CREATE DATABASE [test];
    GO
    CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [test]
    GO
    CREATE USER [test] FOR LOGIN [test]
    GO
    ALTER USER [test] WITH DEFAULT_SCHEMA=[test]
    CREATE SCHEMA [test] AUTHORIZATION [test]
    EXEC sp_addrolemember N'db_backupoperator', N'test'
    EXEC sp_addrolemember N'db_auditor', N'test'
    GO

    SELECT SYSTEM_USER,USER_NAME()
    ,IS_MEMBER('db_backupoperator'),IS_ROLEMEMBER('db_backupoperator')
    ,IS_MEMBER('db_auditor'),IS_ROLEMEMBER('db_auditor');


    I expect

    test test 1 1 1 1

    but i recieve:

    test dbo 1 1 0 0

    What have I to do to assign a user(login) to a specific custom database roles?


    Tuesday, September 24, 2013 8:46 AM

Answers

All replies

  • ALTER USER [test] WITH DEFAULT_SCHEMA=[test]
    CREATE SCHEMA [test] AUTHORIZATION [test]

    You assign first the not existing schema "test" to a user and create then the schema later?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 24, 2013 9:21 AM
  • Of course it would have been impossible.
    I made a mistake when pasting the script.
    To be sure I deleted everything and I generated it again.

    And now it's OK!

    But the problem began with the existing logins and still exists.
    For all logins USER_NAME () returns 'dbo'

    I did an experiment

    I deleted one bad  user, schema and login.

    USE [test]
    GO
    DROP USER [baduser];
    GO

    USE [master]
    GO
    DROP LOGIN [baduser];
    GO

    I created them again:
    CREATE LOGIN [baduser] WITH PASSWORD = N'password ', DEFAULT_DATABASE = [test] CHECK_EXPIRATION = OFF = OFF CHECK_POLICY
    GO
    USE [test]
    GO
    CREATE USER [baduser] FOR LOGIN [baduser]

    And ... no change - USER_NAME () still returns 'dbo'

    In views:

    • sys.server_principals
    • sys.database_principals
    • sys.syslogins
    • sys.sysusers
    • sys.sql_logins

    I do not see any difference between the right user [test] and [baduser].

    What's going on?


    Tuesday, September 24, 2013 10:58 AM
  • USER_NAME (Transact-SQL) without a parameter returns the value for the current user = you, and I guess you are the SysAdmin as well as the database owner = dbo?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Dan GuzmanMVP Sunday, September 29, 2013 5:59 PM
    Tuesday, September 24, 2013 11:18 AM
  • "I guess you are the SysAdmin"

    That's it!

    It's seems that sysadmin always have USER_NAME() = 'dbo' ?

    It follows that you can not assign a specific database role for sysadmins!?

    e.g.

    SELECT SYSTEM_USER,USER_NAME(),IS_MEMBER('db_auditor'),IS_ROLEMEMBER('db_auditor');

    after:

    EXEC sp_addrolemember 'db_auditor', 'test'

    returns: test test 1 1

    but after:

    EXEC sp_addsrvrolemember 'test', 'sysadmin'

    returns: test dbo 0 0

    Ok.

    Thank you for your help :[

    Tuesday, September 24, 2013 11:39 AM
  • It's seems that sysadmin always have USER_NAME() = 'dbo' ?

    It follows that you can not assign a specific database role for sysadmins!?

    A SysAdmin do have all permissions; assigning him to an additional role wouldn't make a difference.

    If you want to query the database username, you have to pass the user id to USER_NAME function like

    SELECT SYSTEM_USER
          ,SUSER_NAME()
          ,USER_NAME()
          ,USER_NAME(2) -- Guest
          ,USER_NAME(USER_ID('guest'))


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Candy_Zhou Thursday, September 26, 2013 8:34 AM
    Tuesday, September 24, 2013 11:52 AM
  • I'm using specific custom (not built-in) roles to establish a rights to certain objects/actions on application level.

    By chance I discovered the problem when I tested the new features on my account.

    I did not know that this applies only sysadmins.

    Sysadmins usually have access to everything.

    This closes the case

    Thank you again :)

    • Proposed as answer by Candy_Zhou Thursday, September 26, 2013 8:34 AM
    Tuesday, September 24, 2013 12:12 PM