Post reply

SYSTEM_USER vs USER_ID(SYSTEM_USER) as default values

  • July 24, 2013 at 5:06 am

    #276236

    Dear,

    I have a huge database with millions of rows all containing SYSTEM_USER value.

    The column value is defined as default

    CREATE DEFAULT [dbo].[A_User] AS SYSTEM_USER

    My idea is to get the ID instead of nvarchar value in order to shrink the table size and have a better query result over ids.

    Now, I have few questions:

    1. I can get the ID's via USER_ID(SYSTEM_USER), trough default column definition. However If I move the users to another table the values might be different so all things will be mixed up

    2. If I use linked servers, (or move the DB to the DR site) will I have problems regarding different IDs

    3. If I want to use sysusers table, I cannot write select nor UDF in the default definition.

    Is there any more intelligent way to get the same functionality another way or should I use the master database and some login values???

    Thanks

    Aleksandar

  • July 24, 2013 at 3:50 pm

    #1635556

    Maybe you should keep the default as it is.

    You could use user_id() and this returns sys.principals.principal_id. The value would not change if the database is moved to a different server. This works well, as long as everyone has a user defined in the database.

    However, in many places this is not likelyt to be the case, but users get access through Windows groups. In that case everyone will map to the same ID.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply