New to Instances: Dont have sa of newly created instance

A new instance has been setup on one of our pilot servers. I do not have th
e
sa password to that instance, and it appears that the group who set up the
instance also removed the built-in/admin group. Am I doomed to never access
this instance?
Should I bring flowers, donoughts AND cookies when begging for the sa
password?I would suggest that you get those that built it to come back and fix it.
The sa password will only help if the thing is running in mixed mode - you
can change this if you are an admin on the windows box by changing the
registry key for LoginMode of the instance (do a search down the
hkey_localmachine\software path) and changing the value to 2, then stopping
and restarting the sql server service.
You could also ask if they set up another admin group before deleting
built-in/admin, and get into it.
Good luck
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"wxd" <wxd@.discussions.microsoft.com> wrote in message
news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>A new instance has been setup on one of our pilot servers. I do not have
>the
> sa password to that instance, and it appears that the group who set up the
> instance also removed the built-in/admin group. Am I doomed to never
> access
> this instance?
> Should I bring flowers, donoughts AND cookies when begging for the sa
> password?|||Thank you for the response. Checking on what you wrote, I found the
LoginMode is already set to 2, and my administrator still receives a "failed
login" message when attempting to connect to the instance through Windows
Authentication. I did not think it was possible to lock out the net admin
group?
"Mary Bray" wrote:

> I would suggest that you get those that built it to come back and fix it.
> The sa password will only help if the thing is running in mixed mode - you
> can change this if you are an admin on the windows box by changing the
> registry key for LoginMode of the instance (do a search down the
> hkey_localmachine\software path) and changing the value to 2, then stoppin
g
> and restarting the sql server service.
> You could also ask if they set up another admin group before deleting
> built-in/admin, and get into it.
> Good luck
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "wxd" <wxd@.discussions.microsoft.com> wrote in message
> news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>
>|||NEVERMIND! Thank you very much for your assistance.
I have found out what this instance is and am discovering that all SQL logic
no longer applies. This is an ACT! installation. ACT! installs an extremel
y
hacked up version of the SQL engine. It is my understanding that the normal
SQL login processes are not present on the server for me to get into this
instance. The company that produces ACT desperately wants people to use
their client vs. any other tool to access their database.
Thank you for your help and insight.
"Mary Bray" wrote:

> I would suggest that you get those that built it to come back and fix it.
> The sa password will only help if the thing is running in mixed mode - you
> can change this if you are an admin on the windows box by changing the
> registry key for LoginMode of the instance (do a search down the
> hkey_localmachine\software path) and changing the value to 2, then stoppin
g
> and restarting the sql server service.
> You could also ask if they set up another admin group before deleting
> built-in/admin, and get into it.
> Good luck
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "wxd" <wxd@.discussions.microsoft.com> wrote in message
> news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>
>

New to Instances: Dont have sa of newly created instance

A new instance has been setup on one of our pilot servers. I do not have the
sa password to that instance, and it appears that the group who set up the
instance also removed the built-in/admin group. Am I doomed to never access
this instance?
Should I bring flowers, donoughts AND cookies when begging for the sa
password?
I would suggest that you get those that built it to come back and fix it.
The sa password will only help if the thing is running in mixed mode - you
can change this if you are an admin on the windows box by changing the
registry key for LoginMode of the instance (do a search down the
hkey_localmachine\software path) and changing the value to 2, then stopping
and restarting the sql server service.
You could also ask if they set up another admin group before deleting
built-in/admin, and get into it.
Good luck
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"wxd" <wxd@.discussions.microsoft.com> wrote in message
news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>A new instance has been setup on one of our pilot servers. I do not have
>the
> sa password to that instance, and it appears that the group who set up the
> instance also removed the built-in/admin group. Am I doomed to never
> access
> this instance?
> Should I bring flowers, donoughts AND cookies when begging for the sa
> password?
|||Thank you for the response. Checking on what you wrote, I found the
LoginMode is already set to 2, and my administrator still receives a "failed
login" message when attempting to connect to the instance through Windows
Authentication. I did not think it was possible to lock out the net admin
group?
"Mary Bray" wrote:

> I would suggest that you get those that built it to come back and fix it.
> The sa password will only help if the thing is running in mixed mode - you
> can change this if you are an admin on the windows box by changing the
> registry key for LoginMode of the instance (do a search down the
> hkey_localmachine\software path) and changing the value to 2, then stopping
> and restarting the sql server service.
> You could also ask if they set up another admin group before deleting
> built-in/admin, and get into it.
> Good luck
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "wxd" <wxd@.discussions.microsoft.com> wrote in message
> news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>
>
|||NEVERMIND! Thank you very much for your assistance.
I have found out what this instance is and am discovering that all SQL logic
no longer applies. This is an ACT! installation. ACT! installs an extremely
hacked up version of the SQL engine. It is my understanding that the normal
SQL login processes are not present on the server for me to get into this
instance. The company that produces ACT desperately wants people to use
their client vs. any other tool to access their database.
Thank you for your help and insight.
"Mary Bray" wrote:

> I would suggest that you get those that built it to come back and fix it.
> The sa password will only help if the thing is running in mixed mode - you
> can change this if you are an admin on the windows box by changing the
> registry key for LoginMode of the instance (do a search down the
> hkey_localmachine\software path) and changing the value to 2, then stopping
> and restarting the sql server service.
> You could also ask if they set up another admin group before deleting
> built-in/admin, and get into it.
> Good luck
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "wxd" <wxd@.discussions.microsoft.com> wrote in message
> news:81522A36-6D2E-4532-9593-2E194D8638EF@.microsoft.com...
>
>

new login, EXECUTE permissions

I'm a newbie to the admin side of SqlServer. I created a new login:
<code>
CREATE LOGIN pmd_app
WITH PASSWORD='********'
</code>
I then used the "Server Management Studio Express" to create a new user in
my DB with the same name, then give the logical permissions, at least
logical to me. I can read and write table data with this new user, but I'm
getting EXECUTE permission errors when calling sprocs. I know how to grant
permissions to a user on a per object basis, but what role memberships
should I be using to give them EXECUTE permissions to all new sprocs that I
create?
I'm looking over BOL to see if I can find the answer, but so far not coming
up with anything.
Also, if anyone knows a good place to find an article covering SQLServer
security, role, permission, schemas, etc that would be awesome ;)
Thanks for any help,
Steve> getting EXECUTE permission errors when calling sprocs. I know how to
> grant permissions to a user on a per object basis, but what role
> memberships
If the user is an owner of the object he/she has an EXECUTE permissions
automatically.
Who is the owner of the object?
"sklett" <sklett@.mddirect.com> wrote in message
news:ePmSgM1NGHA.3732@.TK2MSFTNGP10.phx.gbl...
> I'm a newbie to the admin side of SqlServer. I created a new login:
> <code>
> CREATE LOGIN pmd_app
> WITH PASSWORD='********'
> </code>
>
> I then used the "Server Management Studio Express" to create a new user in
> my DB with the same name, then give the logical permissions, at least
> logical to me. I can read and write table data with this new user, but
> I'm getting EXECUTE permission errors when calling sprocs. I know how to
> grant permissions to a user on a per object basis, but what role
> memberships should I be using to give them EXECUTE permissions to all new
> sprocs that I create?
> I'm looking over BOL to see if I can find the answer, but so far not
> coming up with anything.
> Also, if anyone knows a good place to find an article covering SQLServer
> security, role, permission, schemas, etc that would be awesome ;)
> Thanks for any help,
> Steve
>|||That's a 2000 way of thinking. The new way is to associate everything via
schemas.
Create a schema, grant your users execute permissions in the schema, create
all you new procs under that schema...easy!
"sklett" wrote:
> I'm a newbie to the admin side of SqlServer. I created a new login:
> <code>
> CREATE LOGIN pmd_app
> WITH PASSWORD='********'
> </code>
>
> I then used the "Server Management Studio Express" to create a new user in
> my DB with the same name, then give the logical permissions, at least
> logical to me. I can read and write table data with this new user, but I'm
> getting EXECUTE permission errors when calling sprocs. I know how to grant
> permissions to a user on a per object basis, but what role memberships
> should I be using to give them EXECUTE permissions to all new sprocs that I
> create?
> I'm looking over BOL to see if I can find the answer, but so far not coming
> up with anything.
> Also, if anyone knows a good place to find an article covering SQLServer
> security, role, permission, schemas, etc that would be awesome ;)
> Thanks for any help,
> Steve
>
>|||oooh, uncharted territory! - scary and exciting :)
So it sounds like I need to put my tools down and read the manual. I will
do some Schema research and figure just how they work and what they do.
Thanks for the tip!
"mulhall" <mulhall@.discussions.microsoft.com> wrote in message
news:C6F5B46D-52D0-4EC2-9782-A72A53774A26@.microsoft.com...
> That's a 2000 way of thinking. The new way is to associate everything via
> schemas.
> Create a schema, grant your users execute permissions in the schema,
> create
> all you new procs under that schema...easy!
> "sklett" wrote:
>> I'm a newbie to the admin side of SqlServer. I created a new login:
>> <code>
>> CREATE LOGIN pmd_app
>> WITH PASSWORD='********'
>> </code>
>>
>> I then used the "Server Management Studio Express" to create a new user
>> in
>> my DB with the same name, then give the logical permissions, at least
>> logical to me. I can read and write table data with this new user, but
>> I'm
>> getting EXECUTE permission errors when calling sprocs. I know how to
>> grant
>> permissions to a user on a per object basis, but what role memberships
>> should I be using to give them EXECUTE permissions to all new sprocs that
>> I
>> create?
>> I'm looking over BOL to see if I can find the answer, but so far not
>> coming
>> up with anything.
>> Also, if anyone knows a good place to find an article covering SQLServer
>> security, role, permission, schemas, etc that would be awesome ;)
>> Thanks for any help,
>> Steve
>>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBW9KT4NGHA.1460@.TK2MSFTNGP10.phx.gbl...
>> getting EXECUTE permission errors when calling sprocs. I know how to
>> grant permissions to a user on a per object basis, but what role
>> memberships
> If the user is an owner of the object he/she has an EXECUTE permissions
> automatically.
> Who is the owner of the object?
I don't know :)
if the full name of the object is any indicator ("dbo.usp_MySprocName") I
would have to guess 'dbo' - but I could be wrong. Schemas are brand new to
me, I don't know whay they are or how they work.
Looking at the already defined schemas in my DB, I don't see any obvious
ones that would indicate EXECUTE permissions, I may need to make my own?
Sounds like schemas are my solution, I need to learn about them. Thanks for
the post!
-Steve
>
> "sklett" <sklett@.mddirect.com> wrote in message
> news:ePmSgM1NGHA.3732@.TK2MSFTNGP10.phx.gbl...
>> I'm a newbie to the admin side of SqlServer. I created a new login:
>> <code>
>> CREATE LOGIN pmd_app
>> WITH PASSWORD='********'
>> </code>
>>
>> I then used the "Server Management Studio Express" to create a new user
>> in my DB with the same name, then give the logical permissions, at least
>> logical to me. I can read and write table data with this new user, but
>> I'm getting EXECUTE permission errors when calling sprocs. I know how to
>> grant permissions to a user on a per object basis, but what role
>> memberships should I be using to give them EXECUTE permissions to all new
>> sprocs that I create?
>> I'm looking over BOL to see if I can find the answer, but so far not
>> coming up with anything.
>> Also, if anyone knows a good place to find an article covering SQLServer
>> security, role, permission, schemas, etc that would be awesome ;)
>> Thanks for any help,
>> Steve
>

New login cannot retrieve data from db

Hello all. I created the "student2" user account and set the password to
"student2". I then
went to Enterprise Manager and in the class_mgr database and in its
customers table, I gave this student2 user account access to the database
and set all permissions to the table. But when I log in to SQL Server using
this new userlogin, I can access the SQL Server Query Analyzer OK but when I
run the select * from customers; query I get
http://www.jimwrichards.com/images/student2_1.gif"

.. What have I done wrong or overlooked doing right? Thanks in advance for
any help, Jim.
Jim Richards wrote:

> Hello all. I created the "student2" user account and set the password to
> "student2". I then
> went to Enterprise Manager and in the class_mgr database and in its
> customers table, I gave this student2 user account access to the database
> and set all permissions to the table. But when I log in to SQL Server using
> this new userlogin, I can access the SQL Server Query Analyzer OK but when I
> run the select * from customers; query I get
> http://www.jimwrichards.com/images/student2_1.gif"
> . What have I done wrong or overlooked doing right? Thanks in advance for
> any help, Jim.

What is the default database context for the new user? If it is not
the class_mgr database, then maybe your query is trying to access a
different table of the same name, but which isn't allowed to your user?
Joe Weinstein at BEA
|||"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:6AXCd.483$q4.168@.fe1.texas.rr.com...
> Hello all. I created the "student2" user account and set the password to
> "student2". I then
> went to Enterprise Manager and in the class_mgr database and in its
> customers table, I gave this student2 user account access to the database
> and set all permissions to the table. But when I log in to SQL Server
> using
> this new userlogin, I can access the SQL Server Query Analyzer OK but when
> I
> run the select * from customers; query I get
> http://www.jimwrichards.com/images/student2_1.gif"
> . What have I done wrong or overlooked doing right? Thanks in advance for
> any help, Jim.

No idea, but what are the current permissions on the table? You can use
sp_helprotect to find out (see Books Online for more details):

exec sp_helprotect 'dbo.customers'

Also, did you DENY permissions on that table to student2 or a role which
student2 is in; a DENY takes precedence over a GRANT.

Simon|||Thanks Joe. I set the default database to "class_mgr" which contains the
table "customers" which contains just two rows (customers). As soon as I
access the SQL Query Analyzer using this student2 login the current database
is automatically set to "class_mgr" instead of the usual "master" database.

"Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
news:41DC46D5.5040701@.bea.com...
>
> Jim Richards wrote:
>> Hello all. I created the "student2" user account and set the password to
>> "student2". I then
>> went to Enterprise Manager and in the class_mgr database and in its
>> customers table, I gave this student2 user account access to the database
>> and set all permissions to the table. But when I log in to SQL Server
>> using
>> this new userlogin, I can access the SQL Server Query Analyzer OK but
>> when I
>> run the select * from customers; query I get
>> http://www.jimwrichards.com/images/student2_1.gif"
>>
>> . What have I done wrong or overlooked doing right? Thanks in advance for
>> any help, Jim.
> What is the default database context for the new user? If it is not
> the class_mgr database, then maybe your query is trying to access a
> different table of the same name, but which isn't allowed to your user?
> Joe Weinstein at BEA
>>
>|||Thanks Simon. I have a snapshot of the current permissions:
http://www.jimwrichards.com/images/student2_2.gif.

I did NOT deny any permissions, see snapshot:
http://www.jimwrichards.com/images/student2_3.gif.

What now, Sir? Jim.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41dc4a08$1_1@.news.bluewin.ch...
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:6AXCd.483$q4.168@.fe1.texas.rr.com...
>> Hello all. I created the "student2" user account and set the password to
>> "student2". I then
>> went to Enterprise Manager and in the class_mgr database and in its
>> customers table, I gave this student2 user account access to the database
>> and set all permissions to the table. But when I log in to SQL Server
>> using
>> this new userlogin, I can access the SQL Server Query Analyzer OK but
>> when I
>> run the select * from customers; query I get
>> http://www.jimwrichards.com/images/student2_1.gif"
>>
>> . What have I done wrong or overlooked doing right? Thanks in advance for
>> any help, Jim.
>>
>>
> No idea, but what are the current permissions on the table? You can use
> sp_helprotect to find out (see Books Online for more details):
> exec sp_helprotect 'dbo.customers'
> Also, did you DENY permissions on that table to student2 or a role which
> student2 is in; a DENY takes precedence over a GRANT.
> Simon|||"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:QEYCd.502$q4.238@.fe1.texas.rr.com...
> Thanks Simon. I have a snapshot of the current permissions:
> http://www.jimwrichards.com/images/student2_2.gif.
> I did NOT deny any permissions, see snapshot:
> http://www.jimwrichards.com/images/student2_3.gif.
> What now, Sir? Jim.

Good question - everything seems to be correct, unless I've missed something
somewhere. Is student2 in any roles?

exec sp_helpuser 'student2'

If student2 is in the db_denydatareader role for some reason, then that
would explain what you're seeing. If not, then I'd try dropping and
recreating the user again (and running DBCC CHECKDB wouldn't hurt either,
just in case there is some database corruption).

Simon

new database, set userid and password

I am creating a new application and just created a new database

Application: VB.net 2005/ ASP.net

Database: Sql Server 2005 with four tables

I need to set the userid and password on the database. How do I do that?

I want to be able to create a SQL connection object in my code and I have something like the following:

<CODE>

Dim objconAsNew SqlConnection("server=serverName;uid=;pwd=;database=SomeDatabase")

</CODE>

but the "uid" and the "pwd" are not set on my database. what is an easy way to do this? thanks

This site has complete connection strings in all pop db systems:http://www.connectionstrings.com/

Both Windows Auth and SqlServer Auth has examples in the site.

|||

I recommend that you use windows integrated authentication

Simply make sure that the user identify of the application pool in which your application lives has login rights to your database. Then you don't have to store the password in your config file or your code.

Check this outhttp://weblogs.asp.net/achang/archive/2004/04/15/113866.aspx

Matt

|||

Hi

Yes Matt you are right.

By windows authentication we can achieve this.

First use windows authentication, create user, give him neccessary rights, and mapped this user with your newly created database.

and now in your webconfig provide this username and password to connect this database.

I hope this will helpful u...

|||

Preferably you do not put the username and password in the web.config

If we are using windows integrated authentication, then you want to give permission to the windows account that your application runs under on the database. Then you do not have to worry about password in the config file.