Antivirus install to db server: login

Showing posts with label login. Show all posts

Friday, March 30, 2012

Question about sa on SQL Server 2005

When installing SQL Server 2005 it never asked me for "sa" password. When
trying to login to SQL Server Management Studio, if I want to login as sa,
what is the password for sa ?
Inside SQL Server Management Studio I changed the password for sa, click
OK, and when trying to login to SQL Server Management Studio again with sa
and the password that I just changed it to, it did not let me. It tells me
"Login failed for user sa. Reason: Not associated with a trusted SQL Server
connection. Microsoft SQL Server, error: 18452".
Thank you.Are you using Windows or SQL authentication with SQL 2005?
fniles wrote:
> When installing SQL Server 2005 it never asked me for "sa" password. When
> trying to login to SQL Server Management Studio, if I want to login as sa,
> what is the password for sa ?
> Inside SQL Server Management Studio I changed the password for sa, click
> OK, and when trying to login to SQL Server Management Studio again with sa
> and the password that I just changed it to, it did not let me. It tells me
> "Login failed for user sa. Reason: Not associated with a trusted SQL Server
> connection. Microsoft SQL Server, error: 18452".
> Thank you.
>
--
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email|||During installation it did not ask me if I want to use Windows or SQL
Authentication, should it ?
Since I do not know the password for sa, I logged in using Windows
Authentication.
I also posted this in another thread:
when I go to the SQL Server Management Studio and connect to the
Object Explorer and select my Server (there are 2 options for this),
when I select 1 of the server it says SQL Server 8.0.384 and the other
server it says SQL Server 8.0.194, instead of SQL Server 9.0.xxxx.
Where is my SQL Server 2005 ?
Thank you.
On May 19, 7:16 pm, "Brett I. Holcomb" <brettholc...@.bellsouth.net>
wrote:
> Are you using Windows or SQL authentication with SQL 2005?
> fniles wrote:
> > When installing SQL Server 2005 it never asked me for "sa" password. When
> > trying to login to SQL Server Management Studio, if I want to login as sa,
> > what is the password for sa ?
> > Inside SQL Server Management Studio I changed the password for sa, click
> > OK, and when trying to login to SQL Server Management Studio again with sa
> > and the password that I just changed it to, it did not let me. It tells me
> > "Login failed for user sa. Reason: Not associated with a trusted SQL Server
> > connection. Microsoft SQL Server, error: 18452".
> > Thank you.
> --
> Brett I. Holcomb
> brettholc...@.R777bellsouth.net
> Remove R777 to email|||You can change that from Management studio if I remember correctly.
Did you select the browse and see if SQL 2005 is listed?
fiefie.niles@.gmail.com wrote:
> During installation it did not ask me if I want to use Windows or SQL
> Authentication, should it ?
> Since I do not know the password for sa, I logged in using Windows
> Authentication.
> I also posted this in another thread:
> when I go to the SQL Server Management Studio and connect to the
> Object Explorer and select my Server (there are 2 options for this),
> when I select 1 of the server it says SQL Server 8.0.384 and the other
> server it says SQL Server 8.0.194, instead of SQL Server 9.0.xxxx.
> Where is my SQL Server 2005 ?
> Thank you.
> On May 19, 7:16 pm, "Brett I. Holcomb" <brettholc...@.bellsouth.net>
> wrote:
>> Are you using Windows or SQL authentication with SQL 2005?
>> fniles wrote:
>> When installing SQL Server 2005 it never asked me for "sa" password. When
>> trying to login to SQL Server Management Studio, if I want to login as sa,
>> what is the password for sa ?
>> Inside SQL Server Management Studio I changed the password for sa, click
>> OK, and when trying to login to SQL Server Management Studio again with sa
>> and the password that I just changed it to, it did not let me. It tells me
>> "Login failed for user sa. Reason: Not associated with a trusted SQL Server
>> connection. Microsoft SQL Server, error: 18452".
>> Thank you.
>> --
>> Brett I. Holcomb
>> brettholc...@.R777bellsouth.net
>> Remove R777 to email
>
--
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email|||Thank you.
>You can change that from Management studio if I remember correctly.
Change it to what ?
>Did you select the browse and see if SQL 2005 is listed?
Did you mean browse the "Server Name" ?
When I do that under "Database Engine" there are 2 things listed.
-myComputername\SDK
-myComputername
When I select myComputername\SDK it is SQL Server 8.0.384
When I select myComputername it is SQL Server 8.0.194
Is it possible that I only installed the client portion of the
installation ? I selected everything at installation, and the DVD is
titled "SQL Server 2005 Standard Edition"
On May 19, 9:02 pm, "Brett I. Holcomb" <brettholc...@.bellsouth.net>
wrote:
> You can change that from Management studio if I remember correctly.
> Did you select the browse and see if SQL 2005 is listed?
>
>
> fiefie.ni...@.gmail.com wrote:
> > During installation it did not ask me if I want to use Windows or SQL
> > Authentication, should it ?
> > Since I do not know the password for sa, I logged in using Windows
> > Authentication.
> > I also posted this in another thread:
> > when I go to the SQL Server Management Studio and connect to the
> > Object Explorer and select my Server (there are 2 options for this),
> > when I select 1 of the server it says SQL Server 8.0.384 and the other
> > server it says SQL Server 8.0.194, instead of SQL Server 9.0.xxxx.
> > Where is my SQL Server 2005 ?
> > Thank you.
> > On May 19, 7:16 pm, "Brett I. Holcomb" <brettholc...@.bellsouth.net>
> > wrote:
> >> Are you using Windows or SQL authentication with SQL 2005?
> >> fniles wrote:
> >> When installing SQL Server 2005 it never asked me for "sa" password. When
> >> trying to login to SQL Server Management Studio, if I want to login as sa,
> >> what is the password for sa ?
> >> Inside SQL Server Management Studio I changed the password for sa, click
> >> OK, and when trying to login to SQL Server Management Studio again with sa
> >> and the password that I just changed it to, it did not let me. It tells me
> >> "Login failed for user sa. Reason: Not associated with a trusted SQL Server
> >> connection. Microsoft SQL Server, error: 18452".
> >> Thank you.
> >> --
> >> Brett I. Holcomb
> >> brettholc...@.R777bellsouth.net
> >> Remove R777 to email
> --
> Brett I. Holcomb
> brettholc...@.R777bellsouth.net
> Remove R777 to email- Hide quoted text -
> - Show quoted text -|||Change the authentication from Windows to SQL
When you start Management Studio you get a short list of SQL Servers.
If you click the arrow you get a list which contains "Browse for more".
Clicking on that lists everything it knows about.
If you selected database engine you should have a server.
fiefie.niles@.gmail.com wrote:
> Thank you.
>> You can change that from Management studio if I remember correctly.
> Change it to what ?
>> Did you select the browse and see if SQL 2005 is listed?
> Did you mean browse the "Server Name" ?
> When I do that under "Database Engine" there are 2 things listed.
> -myComputername\SDK
> -myComputername
> When I select myComputername\SDK it is SQL Server 8.0.384
> When I select myComputername it is SQL Server 8.0.194
> Is it possible that I only installed the client portion of the
> installation ? I selected everything at installation, and the DVD is
> titled "SQL Server 2005 Standard Edition"
>
>
> On May 19, 9:02 pm, "Brett I. Holcomb" <brettholc...@.bellsouth.net>
> wrote:
>> You can change that from Management studio if I remember correctly.
>> Did you select the browse and see if SQL 2005 is listed?
>>
>>
>> fiefie.ni...@.gmail.com wrote:
>> During installation it did not ask me if I want to use Windows or SQL
>> Authentication, should it ?
>> Since I do not know the password for sa, I logged in using Windows
>> Authentication.
>> I also posted this in another thread:
>> when I go to the SQL Server Management Studio and connect to the
>> Object Explorer and select my Server (there are 2 options for this),
>> when I select 1 of the server it says SQL Server 8.0.384 and the other
>> server it says SQL Server 8.0.194, instead of SQL Server 9.0.xxxx.
>> Where is my SQL Server 2005 ?
>> Thank you.
>> On May 19, 7:16 pm, "Brett I. Holcomb" <brettholc...@.bellsouth.net>
>> wrote:
>> Are you using Windows or SQL authentication with SQL 2005?
>> fniles wrote:
>> When installing SQL Server 2005 it never asked me for "sa" password. When
>> trying to login to SQL Server Management Studio, if I want to login as sa,
>> what is the password for sa ?
>> Inside SQL Server Management Studio I changed the password for sa, click
>> OK, and when trying to login to SQL Server Management Studio again with sa
>> and the password that I just changed it to, it did not let me. It tells me
>> "Login failed for user sa. Reason: Not associated with a trusted SQL Server
>> connection. Microsoft SQL Server, error: 18452".
>> Thank you.
>> --
>> Brett I. Holcomb
>> brettholc...@.R777bellsouth.net
>> Remove R777 to email
>> --
>> Brett I. Holcomb
>> brettholc...@.R777bellsouth.net
>> Remove R777 to email- Hide quoted text -
>> - Show quoted text -
>
--
Brett I. Holcomb
brettholcomb@.R777bellsouth.net
Remove R777 to email|||Thanks.
I can't change to SQL Authentication because I do not know the
password for sa. During installation it did not ask me for sa's
password.
When I click the "Browse for more" in the the "Server Name" , under
"Database Engine" there are 2 things listed.
-myComputername\SDK
-myComputername
When I select myComputername\SDK it is SQL Server 8.0.384
When I select myComputername it is SQL Server 8.0.194
None of them is SQL 2005.
During installation I selected all of the following:
1. SQL Server Database Services
2. Analysis Services
3. Reporting Services
4.Notification Services
5. Integration Services
6. Workstation components, books online & development tools
My machine is Win XP Professional SP2.
On May 19, 10:27 pm, "Brett I. Holcomb" <brettholc...@.bellsouth.net>
wrote:
> Change the authentication from Windows to SQL
> When you start Management Studio you get a short list of SQL Servers.
> If you click the arrow you get a list which contains "Browse for more".
> Clicking on that lists everything it knows about.
> If you selected database engine you should have a server.
>
>
> fiefie.ni...@.gmail.com wrote:
> > Thank you.
> >> You can change that from Management studio if I remember correctly.
> > Change it to what ?
> >> Did you select the browse and see if SQL 2005 is listed?
> > Did you mean browse the "Server Name" ?
> > When I do that under "Database Engine" there are 2 things listed.
> > -myComputername\SDK
> > -myComputername
> > When I select myComputername\SDK it is SQL Server 8.0.384
> > When I select myComputername it is SQL Server 8.0.194
> > Is it possible that I only installed the client portion of the
> > installation ? I selected everything at installation, and the DVD is
> > titled "SQL Server 2005 Standard Edition"
> > On May 19, 9:02 pm, "Brett I. Holcomb" <brettholc...@.bellsouth.net>
> > wrote:
> >> You can change that from Management studio if I remember correctly.
> >> Did you select the browse and see if SQL 2005 is listed?
> >> fiefie.ni...@.gmail.com wrote:
> >> During installation it did not ask me if I want to use Windows or SQL
> >> Authentication, should it ?
> >> Since I do not know the password for sa, I logged in using Windows
> >> Authentication.
> >> I also posted this in another thread:
> >> when I go to the SQL Server Management Studio and connect to the
> >> Object Explorer and select my Server (there are 2 options for this),
> >> when I select 1 of the server it says SQL Server 8.0.384 and the other
> >> server it says SQL Server 8.0.194, instead of SQL Server 9.0.xxxx.
> >> Where is my SQL Server 2005 ?
> >> Thank you.
> >> On May 19, 7:16 pm, "Brett I. Holcomb" <brettholc...@.bellsouth.net>
> >> wrote:
> >> Are you using Windows or SQL authentication with SQL 2005?
> >> fniles wrote:
> >> When installing SQL Server 2005 it never asked me for "sa" password. When
> >> trying to login to SQL Server Management Studio, if I want to login as sa,
> >> what is the password for sa ?
> >> Inside SQL Server Management Studio I changed the password for sa, click
> >> OK, and when trying to login to SQL Server Management Studio again with sa
> >> and the password that I just changed it to, it did not let me. It tells me
> >> "Login failed for user sa. Reason: Not associated with a trusted SQL Server
> >> connection. Microsoft SQL Server, error: 18452".
> >> Thank you.
> >> --
> >> Brett I. Holcomb
> >> brettholc...@.R777bellsouth.net
> >> Remove R777 to email
> >> --
> >> Brett I. Holcomb
> >> brettholc...@.R777bellsouth.net
> >> Remove R777 to email- Hide quoted text -
> >> - Show quoted text -
> --
> Brett I. Holcomb
> brettholc...@.R777bellsouth.net
> Remove R777 to email- Hide quoted text -
> - Show quoted text -

Wednesday, March 28, 2012

question about removing logins at server level

Hi,
I created a login 'Network service' at server level in Management Studio
express.
I use windows authentification.
Then i defined an user for my database which is associated to login 'Network
service', because the application asp.net uses that account (IIS 6.0). This
user received db_read and db_write roles.
This works.
Now i experimented a little bit and i removed from the logins at server
level the login 'Network service'.
Result: the application still works.
.
Then i removed the Builtin\users login from the login list at server level.
Result: i get the error: "login failed for Network service".
I recreated then the login 'Network Service' at server level but not the
Builtin\users login.
Result: it works again.
My conclusion is: one of the two logins must be in the list: Network Service
or Builtin\users
Is this right?
Why do i get that error when both logins are removed and not only when
Network Service is removed?
Thanks
JeanBecause you're using Windows Authentication. From what you described it
doesn't sound like the connection to the db server ever came in using the
Network Service login, but rather came in using Builtin\users.
"Jean" wrote:
> Hi,
> I created a login 'Network service' at server level in Management Studio
> express.
> I use windows authentification.
> Then i defined an user for my database which is associated to login 'Network
> service', because the application asp.net uses that account (IIS 6.0). This
> user received db_read and db_write roles.
> This works.
> Now i experimented a little bit and i removed from the logins at server
> level the login 'Network service'.
> Result: the application still works.
> ..
> Then i removed the Builtin\users login from the login list at server level.
> Result: i get the error: "login failed for Network service".
> I recreated then the login 'Network Service' at server level but not the
> Builtin\users login.
> Result: it works again.
> My conclusion is: one of the two logins must be in the list: Network Service
> or Builtin\users
> Is this right?
> Why do i get that error when both logins are removed and not only when
> Network Service is removed?
> Thanks
> Jean
>
>|||Thanks, but after deleting the login Network Service, the user still is
mapped to Network Service account, no? If i delete that user, then the
application fails.
"Hurme" <michael.geles@.thomson.com> schreef in bericht
news:1F40DC8A-A258-4900-BD8B-1B312F151ED4@.microsoft.com...
> Because you're using Windows Authentication. From what you described it
> doesn't sound like the connection to the db server ever came in using the
> Network Service login, but rather came in using Builtin\users.
>
> "Jean" wrote:
>> Hi,
>> I created a login 'Network service' at server level in Management Studio
>> express.
>> I use windows authentification.
>> Then i defined an user for my database which is associated to login
>> 'Network
>> service', because the application asp.net uses that account (IIS 6.0).
>> This
>> user received db_read and db_write roles.
>> This works.
>> Now i experimented a little bit and i removed from the logins at server
>> level the login 'Network service'.
>> Result: the application still works.
>> ..
>> Then i removed the Builtin\users login from the login list at server
>> level.
>> Result: i get the error: "login failed for Network service".
>> I recreated then the login 'Network Service' at server level but not the
>> Builtin\users login.
>> Result: it works again.
>> My conclusion is: one of the two logins must be in the list: Network
>> Service
>> or Builtin\users
>> Is this right?
>> Why do i get that error when both logins are removed and not only when
>> Network Service is removed?
>> Thanks
>> Jean
>>|||I'm not sure about whether or not the Network Service id would still be
mapped or not, but I just did a test on one of my sql servers. I previously
did not have the BUILTIN\Users login and tried to login with a domain account
login that did not exist on the sql server nor the windows 2003 server. When
I tried to login access was denied. Once I created the BUILTIN\Users login on
hte sql server I was able to get in.
"Jean" wrote:
> Thanks, but after deleting the login Network Service, the user still is
> mapped to Network Service account, no? If i delete that user, then the
> application fails.
> "Hurme" <michael.geles@.thomson.com> schreef in bericht
> news:1F40DC8A-A258-4900-BD8B-1B312F151ED4@.microsoft.com...
> > Because you're using Windows Authentication. From what you described it
> > doesn't sound like the connection to the db server ever came in using the
> > Network Service login, but rather came in using Builtin\users.
> >
> >
> >
> > "Jean" wrote:
> >
> >> Hi,
> >>
> >> I created a login 'Network service' at server level in Management Studio
> >> express.
> >> I use windows authentification.
> >> Then i defined an user for my database which is associated to login
> >> 'Network
> >> service', because the application asp.net uses that account (IIS 6.0).
> >> This
> >> user received db_read and db_write roles.
> >>
> >> This works.
> >>
> >> Now i experimented a little bit and i removed from the logins at server
> >> level the login 'Network service'.
> >> Result: the application still works.
> >> ..
> >> Then i removed the Builtin\users login from the login list at server
> >> level.
> >> Result: i get the error: "login failed for Network service".
> >>
> >> I recreated then the login 'Network Service' at server level but not the
> >> Builtin\users login.
> >> Result: it works again.
> >>
> >> My conclusion is: one of the two logins must be in the list: Network
> >> Service
> >> or Builtin\users
> >>
> >> Is this right?
> >> Why do i get that error when both logins are removed and not only when
> >> Network Service is removed?
> >>
> >> Thanks
> >> Jean
> >>
> >>
> >>
>
>|||Ok, thanks
"Hurme" <michael.geles@.thomson.com> schreef in bericht
news:6D07EA51-00F3-4787-91A8-95C6BF5C2B4C@.microsoft.com...
> I'm not sure about whether or not the Network Service id would still be
> mapped or not, but I just did a test on one of my sql servers. I
> previously
> did not have the BUILTIN\Users login and tried to login with a domain
> account
> login that did not exist on the sql server nor the windows 2003 server.
> When
> I tried to login access was denied. Once I created the BUILTIN\Users login
> on
> hte sql server I was able to get in.
>
> "Jean" wrote:
>> Thanks, but after deleting the login Network Service, the user still is
>> mapped to Network Service account, no? If i delete that user, then the
>> application fails.
>> "Hurme" <michael.geles@.thomson.com> schreef in bericht
>> news:1F40DC8A-A258-4900-BD8B-1B312F151ED4@.microsoft.com...
>> > Because you're using Windows Authentication. From what you described
>> > it
>> > doesn't sound like the connection to the db server ever came in using
>> > the
>> > Network Service login, but rather came in using Builtin\users.
>> >
>> >
>> >
>> > "Jean" wrote:
>> >
>> >> Hi,
>> >>
>> >> I created a login 'Network service' at server level in Management
>> >> Studio
>> >> express.
>> >> I use windows authentification.
>> >> Then i defined an user for my database which is associated to login
>> >> 'Network
>> >> service', because the application asp.net uses that account (IIS 6.0).
>> >> This
>> >> user received db_read and db_write roles.
>> >>
>> >> This works.
>> >>
>> >> Now i experimented a little bit and i removed from the logins at
>> >> server
>> >> level the login 'Network service'.
>> >> Result: the application still works.
>> >> ..
>> >> Then i removed the Builtin\users login from the login list at server
>> >> level.
>> >> Result: i get the error: "login failed for Network service".
>> >>
>> >> I recreated then the login 'Network Service' at server level but not
>> >> the
>> >> Builtin\users login.
>> >> Result: it works again.
>> >>
>> >> My conclusion is: one of the two logins must be in the list: Network
>> >> Service
>> >> or Builtin\users
>> >>
>> >> Is this right?
>> >> Why do i get that error when both logins are removed and not only when
>> >> Network Service is removed?
>> >>
>> >> Thanks
>> >> Jean
>> >>
>> >>
>> >>
>>

Friday, March 23, 2012

question about login at database level

Hi,

i created at general database level a login 'Network service' (windows
server 2003).
At level of 'mydb' database, i created an user 'aspnet' and i associated it
to the login 'Network service'. That user 'aspnet' receives the necessary
schemas (db_read /db_write) rrquired by the application.

Now, i removed the login 'Network Service' at general database level (not
the user 'aspnet') but the application still runs properly.

My question is: is it allowed to remove a defined login at general database
level (in my case the login 'Network Service')?

Thanks
Jean

You delete the login without affecting the database user.
But at the end you will have a login mapped to a database user

So, yes it is allowed to remove the login.

Good luck.

|||

Thanks

|||

Sorry, i was too fast in my answer.

I did the same on a XP prof. box (removing the login at server level, after defining an user associated to that login at database level).

But now, i get the error :" login failed for user servename\aspnet"

If i recreate that login, it works again.

So it seems that the login cannot be removed at server level?

|||

tartuffe2:
Sorry, i was too fast in my answer.
I did the same on a XP prof. box (removing the login at server level, after defining an user associated to that login at database level).
But now, i get the error :" login failed for user servename\aspnet"
If i recreate that login, it works again.
So it seems that the login cannot be removed at server level?

I guess this is because theservername\aspnet account is not "trusted" by your SQL Server. TSo, the NT account must be granted permission to use your SQL Server database.

You can implement impersonation, and grant that account the needed access.

Check out this link:http://support.microsoft.com/?id=316989

|||

ok, thanks

sql

Friday, March 9, 2012

Question about create database,login,user,schema and grant permissions.

Hi,

I created a database,login,user and schema like belows.

-- 2. create database
CREATE DATABASE MyTempDatabase;

-- 3. create login
CREATE LOGIN MyTempLogin WITH PASSWORD = '#mytemplogin$',
DEFAULT_DATABASE = MyTempDatabase,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;

--
USE MyTempDatabase;

-- 4. create user
CREATE USER MyTempLogin FROM LOGIN MyTempLogin WITH DEFAULT_SCHEMA = MyTempSchema;

-- 5. create schema
CREATE SCHEMA MyTempSchema AUTHORIZATION MyTempLogin;

The created user,MyTempLogin, must have permissions that can create tables,drop tables,select,insert,delete,update and bulk insert.

How can I grant permissions to the user?(or schema?)

I failed to grant by T-SQL query.

Additionally, what is purppose of the ROLE? Should I create or use it?

I'm confusing in security concept(login,user,schema,role).

Thanks.

i will give u a capsule form of all these database objects

Login : SQL Server uses Two level Security architecture. Server level and Database Level. To access database you should have server level access. But if you have server level access it does not mean that you have database level access. So , Login is nothing but the Server Level Security. User is Database Level Security. If you create a login , you should add that login to a database as Database User, to have access to that database.

Role : is just like Groups in Windows. It is to group users for better admistration so that you can grant permission to the role rather than granting to each user. i think you must use this.

Schema : this is a new concept in sql 2005. This basically groups the objects or it is a container. In earlier versions of sql server , objects were owned by user and both were tightlty coupled. So if you want to drop a user then you need to transfer the objects owned by the user to any other user. In sql server 2005 users are not the owner of the objects but schema is the object owner.

i think Books online is the best resource to uderstand these objects. read and practice

Madhu

|||

To allow the user to create and delete tables make them a member of the ddl_admin role within the MyTempDatabase.

Code Snippet

Use MyTempDatabase

exec sp_addrolemember @.rolename='ddl_admin', @.membername='MyTempLogin'

To allow the user to bulk insert data from a text file they need to be a member of the Buk Insert Fixed Server Role. This is a server wide setting which simply allows them to load data from a text file.

Code Snippet

exec sp_addsrvrolemember @.loginame='MyTempLogin', @.rolename='bulkadmin'

Roles are used to make granting permissions to a group of database users easier. Say you have 5 users who all need the same permissions. Instead of setting up there permissions on a user by user basis, you would setup a role and make all the users members of the role. You can then grant the role what ever rights the users need and the users will inheriate those rights from the role. And users can be in more than one role. So if two of your five users need additional rights that the other three don't need you can create a second role, put in the two users and grant that role the extra rights. When this comes into play is when you add a sixth user. You now don't need to remember what rights they need, you simply drop them into the role and they will have the rights.

When using database roles any right which can be granted within a database can be granted to a role.

Friday, March 30, 2012

Question about sa on SQL Server 2005

Question about sa on SQL Server 2005

Question about sa on SQL Server 2005

Wednesday, March 28, 2012

question about removing logins at server level

Friday, March 23, 2012

question about login at database level

Friday, March 9, 2012

Question about create database,login,user,schema and grant permissions.

Antivirus install to db server

Blog Archive

About Me