Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SYSTEM_USER vs USER_ID(SYSTEM_USER) as default values Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 10:43 PM
Points: 4, Visits: 55
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
Post #1476989
Posted Wednesday, July 24, 2013 3:50 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:20 PM
Points: 807, Visits: 725
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1477303
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse