New to SQL and need help

I am running a program that was created in VB that takes data from a
scanned file and inserts it into our database. We have the column
student id as the primary key and it is decremented every time it goes
through the program so they are each unique and never overlap. The
program has been working fine but recently when I run it I get a
primary key violation. I thought that the identity might have gotten
out of sync and tried CheckIdent but still no luck. I came to find
out that the primary key does not have an identity and so I am not
sure why it will not let me insert the data.It is just that the value you are trying to insert already exists.

--
Posted via http://dbforums.com|||I've looked at the table that I am trying to insert into and did a query
for numbers that overlapped and it returned nothing.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hightower130 (bowens@.webebi.com) writes:
> I am running a program that was created in VB that takes data from a
> scanned file and inserts it into our database. We have the column
> student id as the primary key and it is decremented every time it goes
> through the program so they are each unique and never overlap. The
> program has been working fine but recently when I run it I get a
> primary key violation. I thought that the identity might have gotten
> out of sync and tried CheckIdent but still no luck. I came to find
> out that the primary key does not have an identity and so I am not
> sure why it will not let me insert the data.

And since I don't see your database, your input file, your VB code etc,
neither can I tell what went wrong.

It is possible that if you posted some code that it would help, but
since you have the existing data, and the problem appears to be
reproducible - how about debugging it?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

New to SQL

I have a table with a field that is 14 characters long. I have created two more columns with in the same table. I wish to parse each record taking the 14 character field and storing the first 10 characters in one of the newly created fields and take the next 4 characters and store them in the other newly created fields.

Can someone tell me how to do that?Look in BOL (Books Online) at the Left and Right functions.|||I can do the left and right functions. I just don't understand the looping through the records and updating the other fields.|||Run an update query:

UPDATE TableName
SET SecondField = Left(FirstField,10),
ThirdField = Right(FirstField,4)|||Thanks. I was thinking this was going to be more complicated.|||Of course the real question is should you have the other fields in the table, since their values can always be calculated from the first. Generally speaking the answer would be no.

New to M'soft - doing feasibility study of migrating from Brio to

I was really impressed with the ease MSRS reports can be created, but at the
end of it I have come to a roadblock with MSRS not supporting Multi select in
Parameters and also no graphical handle to place the parameters where I want.
So I am concluding that MSRS is not a go forward reporting tool for us.
Do any of you guys think there is a way around'
Also there is no way of jazzing up the reports menu on the Reportserver.
Is there an option of using ASP.net to build the parameters there and then
pass on to the report server' can somebody put down an example.
We have 100 reports to migrate. And I'm running out of time to decide to go
or not to go wit MSRS.. please put in your thoughts/ solutions if any
Thanks
GNUToday you can easily integrate with ASP.Net. You can use either URL
integration or web services. Very strong support for this.
With service pack 2 there will be support for client side printing (SP2 will
be out within the next month or two). The next version of RS will be out
with the next version of SQL Server (Yukon). I am hoping Yukon RS will have
multi-select but I don't know.
I suggest looking at the books on line (you can get to it on the msdn
website I believe). Also, check out all the material available here:
http://www.microsoft.com/sql/reporting/productinfo/default.asp
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GNU" <GNU@.discussions.microsoft.com> wrote in message
news:A7CF6172-BB99-40D5-8573-9C71B3208B22@.microsoft.com...
> I was really impressed with the ease MSRS reports can be created, but at
the
> end of it I have come to a roadblock with MSRS not supporting Multi select
in
> Parameters and also no graphical handle to place the parameters where I
want.
> So I am concluding that MSRS is not a go forward reporting tool for us.
> Do any of you guys think there is a way around'
> Also there is no way of jazzing up the reports menu on the Reportserver.
> Is there an option of using ASP.net to build the parameters there and then
> pass on to the report server' can somebody put down an example.
> We have 100 reports to migrate. And I'm running out of time to decide to
go
> or not to go wit MSRS.. please put in your thoughts/ solutions if any
> Thanks
> GNU
>|||For multi-select parameters, we will have native support for multi-select in
SQL 2005 Reporting Services. You can handle this today by providing your own
parameter UI and dynamically constructing the SQL statement. As for moving
the parameters around, you would have to do this with your own UI.
It is fairly easy to build your own UI in the SQL 2000 version of Reporting
Services using either the Web Services interfaces or URL access. We will
make it even easier in the SQL 2005 version of Reporting Services by using
the new Report Veiwer controls that you can host in your WinForms or ASP.NET
application.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"GNU" <GNU@.discussions.microsoft.com> wrote in message
news:A7CF6172-BB99-40D5-8573-9C71B3208B22@.microsoft.com...
>I was really impressed with the ease MSRS reports can be created, but at
>the
> end of it I have come to a roadblock with MSRS not supporting Multi select
> in
> Parameters and also no graphical handle to place the parameters where I
> want.
> So I am concluding that MSRS is not a go forward reporting tool for us.
> Do any of you guys think there is a way around'
> Also there is no way of jazzing up the reports menu on the Reportserver.
> Is there an option of using ASP.net to build the parameters there and then
> pass on to the report server' can somebody put down an example.
> We have 100 reports to migrate. And I'm running out of time to decide to
> go
> or not to go wit MSRS.. please put in your thoughts/ solutions if any
> Thanks
> GNU
>|||thanks for ur reply.. some more questions..
1) when is SQL 2005 scheduled for release?
2) If I can use ASP.NET will I have to write a lot of code to incorporate
the report in a frame below the asp page so that i can keep changing my
parameters n look up the report data.
3) will it allow export data into Excel/CSV ? as it does when viewed from
report server or will I have to write code for it?
4) where can I find some sample code, I'm new to .Net.
Thanks,
GNU
"Brian Welcker [MSFT]" wrote:
> For multi-select parameters, we will have native support for multi-select in
> SQL 2005 Reporting Services. You can handle this today by providing your own
> parameter UI and dynamically constructing the SQL statement. As for moving
> the parameters around, you would have to do this with your own UI.
> It is fairly easy to build your own UI in the SQL 2000 version of Reporting
> Services using either the Web Services interfaces or URL access. We will
> make it even easier in the SQL 2005 version of Reporting Services by using
> the new Report Veiwer controls that you can host in your WinForms or ASP.NET
> application.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "GNU" <GNU@.discussions.microsoft.com> wrote in message
> news:A7CF6172-BB99-40D5-8573-9C71B3208B22@.microsoft.com...
> >I was really impressed with the ease MSRS reports can be created, but at
> >the
> > end of it I have come to a roadblock with MSRS not supporting Multi select
> > in
> > Parameters and also no graphical handle to place the parameters where I
> > want.
> > So I am concluding that MSRS is not a go forward reporting tool for us.
> > Do any of you guys think there is a way around'
> > Also there is no way of jazzing up the reports menu on the Reportserver.
> > Is there an option of using ASP.net to build the parameters there and then
> > pass on to the report server' can somebody put down an example.
> > We have 100 reports to migrate. And I'm running out of time to decide to
> > go
> > or not to go wit MSRS.. please put in your thoughts/ solutions if any
> > Thanks
> > GNU
> >
> >
>
>|||Answers inline.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"GNU" <GNU@.discussions.microsoft.com> wrote in message
news:2D6F7754-3832-4F25-AAAE-B1F74C4867EA@.microsoft.com...
> thanks for ur reply.. some more questions..
> 1) when is SQL 2005 scheduled for release?
Late summer of 2005.
> 2) If I can use ASP.NET will I have to write a lot of code to incorporate
> the report in a frame below the asp page so that i can keep changing my
> parameters n look up the report data.
Not if you use the Report Viewer controls. You just bind parmeter values in
the control.
> 3) will it allow export data into Excel/CSV ? as it does when viewed from
> report server or will I have to write code for it?
The viewer supports export.
> 4) where can I find some sample code, I'm new to .Net.
There are samples in the product already as well as there will be more
samples in the 2005 release.
> Thanks,
> GNU
>
> "Brian Welcker [MSFT]" wrote:
>> For multi-select parameters, we will have native support for multi-select
>> in
>> SQL 2005 Reporting Services. You can handle this today by providing your
>> own
>> parameter UI and dynamically constructing the SQL statement. As for
>> moving
>> the parameters around, you would have to do this with your own UI.
>> It is fairly easy to build your own UI in the SQL 2000 version of
>> Reporting
>> Services using either the Web Services interfaces or URL access. We will
>> make it even easier in the SQL 2005 version of Reporting Services by
>> using
>> the new Report Veiwer controls that you can host in your WinForms or
>> ASP.NET
>> application.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "GNU" <GNU@.discussions.microsoft.com> wrote in message
>> news:A7CF6172-BB99-40D5-8573-9C71B3208B22@.microsoft.com...
>> >I was really impressed with the ease MSRS reports can be created, but at
>> >the
>> > end of it I have come to a roadblock with MSRS not supporting Multi
>> > select
>> > in
>> > Parameters and also no graphical handle to place the parameters where I
>> > want.
>> > So I am concluding that MSRS is not a go forward reporting tool for us.
>> > Do any of you guys think there is a way around'
>> > Also there is no way of jazzing up the reports menu on the
>> > Reportserver.
>> > Is there an option of using ASP.net to build the parameters there and
>> > then
>> > pass on to the report server' can somebody put down an example.
>> > We have 100 reports to migrate. And I'm running out of time to decide
>> > to
>> > go
>> > or not to go wit MSRS.. please put in your thoughts/ solutions if any
>> > Thanks
>> > GNU
>> >
>> >
>>

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 to database

hello
I have developed applications but never used and created a database. .now I must make a database and I must reach it from 3 or 4 computer.database will not be huge. can I do this freely with sql? if not what do you suggest?

well you want to create a database and access it from someother machines i believe......if i understood it wrongly pls correct me you need to create linked server to access thosedbs from some other machines and run queries on them...

refer, http://technet.microsoft.com/en-us/library/ms186312.aspx > for db creation

http://technet.microsoft.com/en-us/library/ms176061.aspx > using T-SQL

refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6ef578bf-8da7-46e0-88b5-e310fc908bb0.htm about linked servers

|||To speak in common, SQL Server is the scalable RDBMS of Microsoft. It scales up to thousands of clients and connections at a time. YOu should start at the http://www.microsoft.com/sql to see all the deature SQL Server provides. To keep a short answer, yes this could be the appropiate solution for you. Depending on your needs you will have to pick one edition from the available one which fits your needs (and budget).

See the different editions here: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

new tempdb file shrinking

since the tempdb file was growing to large I created a new one on another
drive; now that file is getting to large; I need to shrink it. both the
original "tempdev" file and the new one "tempdb_1_data" are in the PRIMARY
group but the "tempdev" is locked to 1 MB while "tempdb_1_data" is now 36GB
and growing. only about 1GB of the 36GB is used.
remember i don't want to shrink the original sql server created temp file
"tempdev"
can I use dbcc shrinkfile (tempdb_1_data, 2000) without a problem? and
without having to restart the sql service?
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter L." <Peter L.@.discussions.microsoft.com> wrote in message
news:8FD556CF-ED9A-41BD-80EA-6C5E604EEEBB@.microsoft.com...
> since the tempdb file was growing to large I created a new one on another
> drive; now that file is getting to large; I need to shrink it. both the
> original "tempdev" file and the new one "tempdb_1_data" are in the PRIMARY
> group but the "tempdev" is locked to 1 MB while "tempdb_1_data" is now
36GB
> and growing. only about 1GB of the 36GB is used.
> remember i don't want to shrink the original sql server created temp file
> "tempdev"
> can I use dbcc shrinkfile (tempdb_1_data, 2000) without a problem? and
> without having to restart the sql service?

new tempdb file shrinking

since the tempdb file was growing to large I created a new one on another
drive; now that file is getting to large; I need to shrink it. both the
original "tempdev" file and the new one "tempdb_1_data" are in the PRIMARY
group but the "tempdev" is locked to 1 MB while "tempdb_1_data" is now 36GB
and growing. only about 1GB of the 36GB is used.
remember i don't want to shrink the original sql server created temp file
"tempdev"
can I use dbcc shrinkfile (tempdb_1_data, 2000) without a problem? and
without having to restart the sql service?http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter L." <Peter L.@.discussions.microsoft.com> wrote in message
news:8FD556CF-ED9A-41BD-80EA-6C5E604EEEBB@.microsoft.com...
> since the tempdb file was growing to large I created a new one on another
> drive; now that file is getting to large; I need to shrink it. both the
> original "tempdev" file and the new one "tempdb_1_data" are in the PRIMARY
> group but the "tempdev" is locked to 1 MB while "tempdb_1_data" is now
36GB
> and growing. only about 1GB of the 36GB is used.
> remember i don't want to shrink the original sql server created temp file
> "tempdev"
> can I use dbcc shrinkfile (tempdb_1_data, 2000) without a problem? and
> without having to restart the sql service?

new tempdb file shrinking

since the tempdb file was growing to large I created a new one on another
drive; now that file is getting to large; I need to shrink it. both the
original "tempdev" file and the new one "tempdb_1_data" are in the PRIMARY
group but the "tempdev" is locked to 1 MB while "tempdb_1_data" is now 36GB
and growing. only about 1GB of the 36GB is used.
remember i don't want to shrink the original sql server created temp file
"tempdev"
can I use dbcc shrinkfile (tempdb_1_data, 2000) without a problem? and
without having to restart the sql service?http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"Peter L." <Peter L.@.discussions.microsoft.com> wrote in message
news:8FD556CF-ED9A-41BD-80EA-6C5E604EEEBB@.microsoft.com...
> since the tempdb file was growing to large I created a new one on another
> drive; now that file is getting to large; I need to shrink it. both the
> original "tempdev" file and the new one "tempdb_1_data" are in the PRIMARY
> group but the "tempdev" is locked to 1 MB while "tempdb_1_data" is now
36GB
> and growing. only about 1GB of the 36GB is used.
> remember i don't want to shrink the original sql server created temp file
> "tempdev"
> can I use dbcc shrinkfile (tempdb_1_data, 2000) without a problem? and
> without having to restart the sql service?sql

New Sysadmin cannot stop SQL instance...

Currently, I'm running SQL Server 2000 on Windows 2000
Advanced Server. I created a new Windows user who is not
in the Administrators group. Then, I entered into the SQL
instance's properties and changed the startup account to
be this new user's credentials. As a result, SQL created a
login for this new user and added him to the System
Administrators server role. When I log into the system as
this new user, I'm able to start the instance but I'm not
able to stop it. I don't understand this behavior since a
System administrator has SHUTDOWN privileges. Any
assistance is greatly appreciated.
Sincerely,
ABGYou will need to give the user rights to start/stop services.
Many dbas have administrative access on the windows boxes
used as sql servers.
"ABG" <abimael.garcia@.unisys.com> wrote in message
news:060501c34a59$86410a20$a101280a@.phx.gbl...
> Currently, I'm running SQL Server 2000 on Windows 2000
> Advanced Server. I created a new Windows user who is not
> in the Administrators group. Then, I entered into the SQL
> instance's properties and changed the startup account to
> be this new user's credentials. As a result, SQL created a
> login for this new user and added him to the System
> Administrators server role. When I log into the system as
> this new user, I'm able to start the instance but I'm not
> able to stop it. I don't understand this behavior since a
> System administrator has SHUTDOWN privileges. Any
> assistance is greatly appreciated.
> Sincerely,
> ABG
>|||Thank you for your response. I'm using Windows 2000
Advanced Server and I don't see the user right (start/stop
service). I do see (Logon as a service) and the user
already has that privilege. I purposely don't want to make
this user an administrator of the system but I do want him
to be able to shutdown the SQL Server instance. Do you
have any other suggestions?
>--Original Message--
>You will need to give the user rights to start/stop
services.
>Many dbas have administrative access on the windows boxes
>used as sql servers.
>"ABG" <abimael.garcia@.unisys.com> wrote in message
>news:060501c34a59$86410a20$a101280a@.phx.gbl...
>> Currently, I'm running SQL Server 2000 on Windows 2000
>> Advanced Server. I created a new Windows user who is not
>> in the Administrators group. Then, I entered into the
SQL
>> instance's properties and changed the startup account to
>> be this new user's credentials. As a result, SQL
created a
>> login for this new user and added him to the System
>> Administrators server role. When I log into the system
as
>> this new user, I'm able to start the instance but I'm
not
>> able to stop it. I don't understand this behavior since
a
>> System administrator has SHUTDOWN privileges. Any
>> assistance is greatly appreciated.
>> Sincerely,
>> ABG
>
>.
>|||Unfortunately I don't know what rights you need to assign, but
would question why you believe you cannot trust the person
responsible for corporate data with your network.
"ABG" <abimael.garcia@.unisys.com> wrote in message
news:017201c34bb9$248932b0$a301280a@.phx.gbl...
> Thank you for your response. I'm using Windows 2000
> Advanced Server and I don't see the user right (start/stop
> service). I do see (Logon as a service) and the user
> already has that privilege. I purposely don't want to make
> this user an administrator of the system but I do want him
> to be able to shutdown the SQL Server instance. Do you
> have any other suggestions?
>
>
>
> >--Original Message--
> >You will need to give the user rights to start/stop
> services.
> >
> >Many dbas have administrative access on the windows boxes
> >used as sql servers.
> >
> >"ABG" <abimael.garcia@.unisys.com> wrote in message
> >news:060501c34a59$86410a20$a101280a@.phx.gbl...
> >>
> >> Currently, I'm running SQL Server 2000 on Windows 2000
> >> Advanced Server. I created a new Windows user who is not
> >> in the Administrators group. Then, I entered into the
> SQL
> >> instance's properties and changed the startup account to
> >> be this new user's credentials. As a result, SQL
> created a
> >> login for this new user and added him to the System
> >> Administrators server role. When I log into the system
> as
> >> this new user, I'm able to start the instance but I'm
> not
> >> able to stop it. I don't understand this behavior since
> a
> >> System administrator has SHUTDOWN privileges. Any
> >> assistance is greatly appreciated.
> >> Sincerely,
> >> ABG
> >>
> >
> >
> >.
> >|||This is purely for testing and research purposes. I'm
trying to determine security best practices for multiple
SQL instance scenarios. In a SQL consolidation case,
politics becomes an issue: one department owns one
instance and another department may govern another. So,
internal security and isolated administration becomes an
issue. Basically, I was trying to setup a case where there
are completely separate sysadmins for each instance
inwhich they cannot affect the operations of the other
instances i.e. startup/shutdown the other instances.
I wanted a sysadmin of an instance to be able to
perform all tasks on his instance including
startup/shutdown. But I didn't want that sysadmin to have
full administrative rights on the whole system. Now it
appears that he must be an Administrator of the system in
order to do this. And this is very interesting because by
default the Builtin\Administrators windows group is added
to the System Administrators role. Therefore, if all
sysadmin users from all instances are also a part of the
Administrators windows group then they can essentially
perform administrative activities on the other instances.
And this is what I'm trying to prevent. Thank you for all
your help.
Sincerely,
ABG
>--Original Message--
>Unfortunately I don't know what rights you need to
assign, but
>would question why you believe you cannot trust the person
>responsible for corporate data with your network.
>"ABG" <abimael.garcia@.unisys.com> wrote in message
>news:017201c34bb9$248932b0$a301280a@.phx.gbl...
>> Thank you for your response. I'm using Windows 2000
>> Advanced Server and I don't see the user right
(start/stop
>> service). I do see (Logon as a service) and the user
>> already has that privilege. I purposely don't want to
make
>> this user an administrator of the system but I do want
him
>> to be able to shutdown the SQL Server instance. Do you
>> have any other suggestions?
>>
>>
>>
>> >--Original Message--
>> >You will need to give the user rights to start/stop
>> services.
>> >
>> >Many dbas have administrative access on the windows
boxes
>> >used as sql servers.
>> >
>> >"ABG" <abimael.garcia@.unisys.com> wrote in message
>> >news:060501c34a59$86410a20$a101280a@.phx.gbl...
>> >>
>> >> Currently, I'm running SQL Server 2000 on Windows
2000
>> >> Advanced Server. I created a new Windows user who is
not
>> >> in the Administrators group. Then, I entered into the
>> SQL
>> >> instance's properties and changed the startup
account to
>> >> be this new user's credentials. As a result, SQL
>> created a
>> >> login for this new user and added him to the System
>> >> Administrators server role. When I log into the
system
>> as
>> >> this new user, I'm able to start the instance but I'm
>> not
>> >> able to stop it. I don't understand this behavior
since
>> a
>> >> System administrator has SHUTDOWN privileges. Any
>> >> assistance is greatly appreciated.
>> >> Sincerely,
>> >> ABG
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

new stored procedures created as type system instead of user

I am running SQL 2000 SP4. When I create a new stored procedure it is
being created as type system instead of user. How can I make it so
every new sp I create is type user instead of system? Thanks in
advance.Hi
"ginarunco@.hotmail.com" wrote:

> I am running SQL 2000 SP4. When I create a new stored procedure it is
> being created as type system instead of user. How can I make it so
> every new sp I create is type user instead of system? Thanks in
> advance.
>
Run DBCC TRACESTATUS ( 1717 )
If the flag is on, run
EXEC sp_MS_upd_sysobj_category 2
Check the SQL Server Startup parameters to make sure it doesn't turn this
trace flag on at startup.
John

new stored procedures created as type system instead of user

I am running SQL 2000 SP4. When I create a new stored procedure it is
being created as type system instead of user. How can I make it so
every new sp I create is type user instead of system? Thanks in
advance.
Hi
"ginarunco@.hotmail.com" wrote:

> I am running SQL 2000 SP4. When I create a new stored procedure it is
> being created as type system instead of user. How can I make it so
> every new sp I create is type user instead of system? Thanks in
> advance.
>
Run DBCC TRACESTATUS ( 1717 )
If the flag is on, run
EXEC sp_MS_upd_sysobj_category 2
Check the SQL Server Startup parameters to make sure it doesn't turn this
trace flag on at startup.
John
sql

new stored procedures created as type system instead of user

I am running SQL 2000 SP4. When I create a new stored procedure it is
being created as type system instead of user. How can I make it so
every new sp I create is type user instead of system? Thanks in
advance.Hi
"ginarunco@.hotmail.com" wrote:
> I am running SQL 2000 SP4. When I create a new stored procedure it is
> being created as type system instead of user. How can I make it so
> every new sp I create is type user instead of system? Thanks in
> advance.
>
Run DBCC TRACESTATUS ( 1717 )
If the flag is on, run
EXEC sp_MS_upd_sysobj_category 2
Check the SQL Server Startup parameters to make sure it doesn't turn this
trace flag on at startup.
John

New SQL Server 2000 Install - Backup of Empty Database never compl

We installed SQL Server 2000 SP3 and created a number of databases. However
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimber
mikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:

> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>

New SQL Server 2000 Install - Backup of Empty Database never compl

We installed SQL Server 2000 SP3 and created a number of databases. However
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimbermikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>

New Pull Subscription - Trans Repli - Use old snapshot...

How do I created a new Pull subscription from a transaction replication
publisher without having to run the Sanpshot again?
When I create a new Pull Subscription, it says "Snapshot not ready". After I
run Sanpshot, then it starts.
Running the snapshot forces me to take the production server down for 2
hours, and can not always do this.
Thanks and God Bless,
ThomBeaux
use the concurrent snapshot option. God bless u2.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
news:60B3F87F-ED59-4030-B30B-E61B145754C5@.microsoft.com...
> How do I created a new Pull subscription from a transaction replication
> publisher without having to run the Sanpshot again?
> When I create a new Pull Subscription, it says "Snapshot not ready". After
> I
> run Sanpshot, then it starts.
> Running the snapshot forces me to take the production server down for 2
> hours, and can not always do this.
> --
> Thanks and God Bless,
> ThomBeaux
|||What part on the Wizard asks for for the concurrent snapshot optin
Thanks and God Bless,
ThomBeaux
"Hilary Cotter" wrote:

> use the concurrent snapshot option. God bless u2.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
> news:60B3F87F-ED59-4030-B30B-E61B145754C5@.microsoft.com...
>
>
|||As well as Hilary's advice, if you are using SQL Server 2005 there is the
option to "initialize with backup". In the CTP SP2 there is also the option
of 'database snapshot' for the sync type AFAIR.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||after creating your publication right click on it and go to the snapshot
tab, you should find it there. Its something about minimize locking during
snapshot generation.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ThomBeaux" <ThomBeaux@.NoSpam.Com> wrote in message
news:EDD53985-2C00-4151-8902-C08AAC5CEE52@.microsoft.com...[vbcol=seagreen]
> What part on the Wizard asks for for the concurrent snapshot optin
> --
> Thanks and God Bless,
> ThomBeaux
>
> "Hilary Cotter" wrote:

new publication deleted before snapshot completed

I just created a new publication and subscription... And was in the middle
of the synchronization when I dropped the publication...
I am now receiving a 'Could not find stored procedure 'sp_MSins_tablename''
for all of the agents replicating to this server/db.
The error code is 2812.
Anyone encounter this and have an easy solution?
Thanks in advance!
~lb
Hi,
You can remove all the entries manually or use sp_removedbreplication
'databaseName' to start all over again.
Hope this helps.
cheers
"Lonnye Blake Bower" wrote:

> I just created a new publication and subscription... And was in the middle
> of the synchronization when I dropped the publication...
> I am now receiving a 'Could not find stored procedure 'sp_MSins_tablename''
> for all of the agents replicating to this server/db.
> The error code is 2812.
> Anyone encounter this and have an easy solution?
> Thanks in advance!
> ~lb
>
>

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, 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...[vbcol=seagreen]
> 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:
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBW9KT4NGHA.1460@.TK2MSFTNGP10.phx.gbl...
> 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...
>

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 gran
t
> 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 comin
g
> 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...[vbcol=seagreen]
> 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:
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBW9KT4NGHA.1460@.TK2MSFTNGP10.phx.gbl...
> 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...
>