Hi Experts, Could you pls expalin me is the database ID is basically an OS ID that is then given DB access, or is the ID created within the database itself Can i create a Database user only, without creating a windows login id. Or first create a windows id and then create a Database user and then map db user to login id. Thanks in advance.
The database ID is assigned by SQL Server when the database is initially created. It has nothing to do with the OS itself as far as I know. The OS doesn't even know that it exists outside of SQL Server. If you are using SQL Server 2012 or higher, you can create a contained database. A contained database partially refers to the fact that all of the users (and subsequent authentication of said users) happens at the database level not the SQL level. This would allow you to create a database user without an associated sql server login if you wish. Reference:
https://msdn.microsoft.com/en-us/library/ff929071.aspx IMHO, I would create a windows account (active directory), create the login, and then the associated database user mapped to the new login. Hope that helps!