About Login Account

Hi everyone,
I have a simple question for you which is too important for me.
My question is about logins in my own(local) server.
I know that my local server(my computer's server) has to default login account which are called sa and BUILTIN/ADMINISTRATOR. While I am using my own server, I do not know which default login account is used by my Server since I use windows authentication while connecting to the Server.
Another question is that while adding new login account for my own Server, I noticed that there is a choice which is comprised of Grant and Deny for the Security. What is the meaning and usage of this ?

Thanks and Regards

I suggest reading some of the security papers of Microsoft or other sites covering SQL Server Authentication, like

http://www.developer.com/tech/article.php/721441

For the beginning , sa is the sysadminstrator account (and which is a SQL Server login), Builtin/Adminstrators is a WIndows groups which is also in the role of the sysadmins by default.

You can either Grant Access to a SQL Server database or Deny access. Grant permissions are always additive, deny is restricitve, which means that if a user is in a special Windows group which is allowed to access the server but is in another group which is denied access to the server, access will be denied to him.

Jens SUessmeyer.

http://www.sqlserver2005.de

|||

Thanks for your good tutorial reference but I could not understand the following paragraph.
Would you mind explaining it to me clearly ?

With Windows Authentication we recommending placing users into NT groups and adding the NT group login to SQL Server. This powerful feature allows you to group your users together at the NT Domain level and apply permissions at the SQL Server level. For example a group named sales could exist in the NT Domain. A new login based on the NT sales group could be added to SQL Server. When a new NT account is created the NT administrator can add the new account to the NT group named sales. The SQL Server DBA can apply the appropriate role(s) and permissions to the sales group. The new NT login automatically contains the necessary permissions to log in to SQL Server because the NT sales group was previously granted a login. This strategy makes it easy for new users to be added the system.

|||

Sure,

assume the following situation. You have a production department with 5000 employees. One day you create a table storing information about timesheet. you want every employee to enter his worked time into that table. You could either grant all 5000 employees individually rights on the table to insert data or you can use the possible existing Windows group which already includes all the 500 employees and which is maintained through the active directory. So if any user will leave the company / production you don′t have to keep track as a database adminstrator of the security, because as the employee is deleted from the group he will have no longer access to the table. So you only have to do the work at a single point, the Active diretory.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Indeed, thanks very much for your explanatory reply but I wonder that how I can create a Windows group authentication.

|||Hi,

first some more to read :-) http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1061.mspx?mfr=true

If you have a domain simply create a Windows group, put user in and create a WIndows login in SQL Server naming the group. Although the creation dialog is named Windows login you can both create a Windows login from a user or a group.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for your explanatory reply, but I have still some doubts about Windows Authentication.
Is it really possible to use Windows Authentication in order to connect an another SQL Server in my computer ? I think that we can only use Windows Authentication mode for our own local server, is it true ?

|||

Jens Suessmeyer wrote:

I suggest reading some of the security papers of Microsoft or other sites covering SQL Server Authentication, like

http://www.developer.com/tech/article.php/721441

For the beginning , sa is the sysadminstrator account (and which is a SQL Server login), Builtin/Adminstrators is a WIndows groups which is also in the role of the sysadmins by default.

You can either Grant Access to a SQL Server database or Deny access. Grant permissions are always additive, deny is restricitve, which means that if a user is in a special Windows group which is allowed to access the server but is in another group which is denied access to the server, access will be denied to him.

Jens SUessmeyer.

http://www.sqlserver2005.de

Another question is that I connect my own SQL Server with Windows Authentication mode.
When I tried to connect with SQL server Authentication mode, I could not accomplish it.
So for uor own local server, is not it allowed to connect SQL Server with SQL server Authentication mode.
Besides, As I said before, I connect my own SQL Server with Windows Authentication mode, so does this mean that I use default Builtin/Adminstrators login in order to connect ?
|||

Yes you are appearantly using Windows authentication. The mentioned group is configured by default to have access to SQL Server. If you can′t access your Server using SQL Server authentication you might not have switch the authentication mode to mixed. For that navigate in the managment Studio to the server properties, in the security tabs and switch to "Mixed Mode", you should be able to connect with any SQL Server login then (by default there is a user with the name sa (system administrator). (make sure you restart your SQL Server in order to apply the changes)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Again thanks vey much for your explanatory reply and would you mind also replying my second question since they make me very confused ?

Thanks and Best wishes

|||

Mhmm, don′t really know which question you mean but if you mean the BUILTIN\Administrators question: YOu can anser this on your own, are you in the administator group ? If yes, you are currently using that. You can have several group memberships all the permissions set in these groups are additive as long as you will get a permission set which denys anything to you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hmm, thanks but this is not really the answer of my question.
I asked that in my local server, while I am connecting to this (my local server(my computer's server) ) , which role do I have , sa(Sytem Admistrator) or BUILTIN/ ADMISTRATOR.
And besides, I am connecting to my own local SQL server with windows Authentication so does it means that I am BUILTIN/ ADMISTRATOR ?

Thanks

|||

Jens Suessmeyer wrote:

Sure,

assume the following situation. You have a production department with 5000 employees. One day you create a table storing information about timesheet. you want every employee to enter his worked time into that table. You could either grant all 5000 employees individually rights on the table to insert data or you can use the possible existing Windows group which already includes all the 500 employees and which is maintained through the active directory. So if any user will leave the company / production you don′t have to keep track as a database adminstrator of the security, because as the employee is deleted from the group he will have no longer access to the table. So you only have to do the work at a single point, the Active diretory.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

My another question is that as I understood form your reply, firslt we should create a domain name for this Window Group. Then, we add all our corresponding users into this domain while using Windows Authentication for our group. So I ask that how can I create a group( domain name) ?

Again thanks.
Sorry for asking too much question but they really make me confused too much.
Regards|||YOu can either login using SQL Server authentication, then you have to specify a SQL Server account and apssword during the login OR you can use the Integrated security (you don′t need to provide the credentials then because it is integrated and uses your Windows context)

I don′t know in which groups you belong on your local server, but if you are using the default settings, didn′t grant any other group access to the SQL Server (and though this is your own server) I assume that you use the group memebership of BUILTIN \Administrator to authenticate against the server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Jens Suessmeyer wrote:

YOu can either login using SQL Server authentication, then you have to specify a SQL Server account and apssword during the login OR you can use the Integrated security (you don′t need to provide the credentials then because it is integrated and uses your Windows context)

I don′t know in which groups you belong on your local server, but if you are using the default settings, didn′t grant any other group access to the SQL Server (and though this is your own server) I assume that you use the group memebership of BUILTIN \Administrator to authenticate against the server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Again and again thank very much for your all extreme explanatory replies.

Now I think I kept the idea. As I understood, By default, I connect my own local server with Windows Authentication as a BUILTIN \Administrator.This group is not regarded as a Group in my opinion. Because, we can not add new user into this group. There is only one person who is the owner of the local SQL server(me) in this group. So what is the real advantage of this group ?
Meanwhile, how can I learn my sa(System administrator)'s password in order to connect my own local SQL Server as a sa(System administrator) ?

Thanks