Asynchronous Batch Processing

Currently we have a process that does synchronous batch processing that can
take a long time and the user cannot do anything while this is happening. W
e
want to encapsulate this process into an asynchronous operation. I imagine
that the processing itself will now reside in a DTS package (SSIS package
actually, since we will be using SQL Server 2005).
Scheduling a package to run asynchronously is no problem, but it would be
nice if it notified the calling app (which is UNIFACE btw - I don't know a
lot about it, so don't ask me) that the batch process is complete. We could
have the UNIFACE app poll to check the status, but I was wondering if Servic
e
Broker could help in this capacity or would that be overkill?
Ultimately, we will be replacing the UNIFACE app with our own suite, and
want the process to be as modular as possible, to facilitate a painless
conversion.
Thanks,
BrandonBrandon,
There are a number of reasons which I believe make Service Broker specially
qualified for this kind of jobs (i.e. asynchronous execution). Being
entirely contained in the database and is running inside the SQL Server
process allows Service Broker based apps to benefit from backup/restore (the
state of your jobs is backed up as part of the database), from
failover/clustering and from database mirroring (the job schedule just fails
over along with the database). Service Broker also gives you a mean to
communicate back from this jobs to the calling app (dialogs are always
bidirectional, the job can reply back on the same dialog that started the
job). Also you'll benefit from the poll free model of the Service Broker:
WAITFOR (RECEIVE ...) does not poll, it blocks until a message becomes
available.
Another nice feature of Service Broker is that it can give you persisted
timers (BEGIN CONVERSATION TIMER ...), stored in the database (again,
benefiting from all the backup/restore and availability benefits of
databases)
What are you afraid of when you say that Service Broker would be overkill?
HTH,
~ Remus
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:0F6D5B45-7A24-4539-BE76-E5B8675A3E83@.microsoft.com...
> Currently we have a process that does synchronous batch processing that
> can
> take a long time and the user cannot do anything while this is happening.
> We
> want to encapsulate this process into an asynchronous operation. I
> imagine
> that the processing itself will now reside in a DTS package (SSIS package
> actually, since we will be using SQL Server 2005).
> Scheduling a package to run asynchronously is no problem, but it would be
> nice if it notified the calling app (which is UNIFACE btw - I don't know a
> lot about it, so don't ask me) that the batch process is complete. We
> could
> have the UNIFACE app poll to check the status, but I was wondering if
> Service
> Broker could help in this capacity or would that be overkill?
> Ultimately, we will be replacing the UNIFACE app with our own suite, and
> want the process to be as modular as possible, to facilitate a painless
> conversion.
> Thanks,
> Brandon|||Mainly I am hesitant for two reasons... I am not that familiar with the
capabilities of UNIFACE (from what I understand it would have to poll instea
d
of using a blocking call like WAITFOR to determine whether job
completed/status of job) and also that I have only had minimal experience
with Service Broker (in the form of the several very simply demos out there)
.
Since the UNIFACE interface will eventually be replaced by a Delphi.NET app,
I can more easily see how that would work better in the long term.
Have you seen any Service Broker examples that communicate with a SSIS
package?
Thanks,
Brandon|||I'm gonna have to do some research about SSIS to see how it integrates with
SSB
What kind of asynchronous batch is gonna be processed? Are talking about
launching an external process, calling a stored proc, running a t-sql batch,
calling an CLR stored procedure?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon|||Two Connect has a sample of Service Broker custom tasks for SSIS:
[url]http://www.twoconnect.com/pages/product_solutions/sqlserver.enhancements.ASPX[/url
]
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon

Asterisk in SQL

I'm trying to creat a search form and want to use the Like clause in my select statement so that a user can enter part of a word rather than the entire word. When I use this sql I get no results:

SELECT DISTINCT Keyword.CodeID FROM Keyword INNER JOIN Code ON Keyword.CodeID = Code.CodeID WHERE ((Keyword.Keyword)Like '*ARR*'AND (Code.ProgLang)='VB.NET') ORDER BY Keyword.CodeID

The problem is with the * . If I remove the * it works fine. If I use the code within Access rather than from my aspx code, it works fine. Is there a work around for this?

Hi,

% (percent) is the standard wildcard character. Access client does support *, but when you use it via OleDB (Jet provider) it also requires %.

Therefore put

SELECT DISTINCT Keyword.CodeID FROM Keyword INNER JOIN Code ON Keyword.CodeID = Code.CodeID WHERE ((Keyword.Keyword)Like '%ARR%'AND (Code.ProgLang)='VB.NET') ORDER BY Keyword.CodeID

|||That did it. Thanks!!!

Associating a Windows account with a user

Hi,
I need to associate an existing Windows account with an existing SQL Server
2000 user, but have not found the stored procedure for this.
Can somebody guide me on this please?
Thanks in advance,
Juan Dent, M.Sc.Is this the case of an orphanned user, due to moving the db from one domain
to another or something? There is a procedure called sp_change_users_login,
but that will not work with Windows accounts.
Can you simply grant access to that Windows login to SQL Server using
sp_grantlogin, and add associated user in the database using
sp_grantdbaccess? You'll have to apply the permissions manually though.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Juan Dent" <JuanDent@.discussions.microsoft.com> wrote in message
news:5AF26949-D98D-4F0A-9F6E-2DF92469E88C@.microsoft.com...
> Hi,
> I need to associate an existing Windows account with an existing SQL
Server 2000 user, but have not found the stored procedure for this.
> Can somebody guide me on this please?
> --
> Thanks in advance,
> Juan Dent, M.Sc.|||Actually what I have is a database with three users but only one of them is
associated with a Windows account.
Looking in SQLSErver Enterprise Manager, in the Users folder of the database
, it looks something like this:
dbo DENTDEVELOPMENT\JuanDent
isadmin
isuser
See? What I want is to associate the login above with isadmin as well as dbo
.
Is that possible?
Thanks in advance,
Juan Dent, M.Sc.
"Narayana Vyas Kondreddi" wrote:

> Is this the case of an orphanned user, due to moving the db from one domai
n
> to another or something? There is a procedure called sp_change_users_login
,
> but that will not work with Windows accounts.
> Can you simply grant access to that Windows login to SQL Server using
> sp_grantlogin, and add associated user in the database using
> sp_grantdbaccess? You'll have to apply the permissions manually though.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Juan Dent" <JuanDent@.discussions.microsoft.com> wrote in message
> news:5AF26949-D98D-4F0A-9F6E-2DF92469E88C@.microsoft.com...
> Server 2000 user, but have not found the stored procedure for this.
>
>|||No actually what I have, as seen in SQLSERVER Enterprise Manager in the User
's folder is:
dbo DENTDEVELOPMENT\JuanDent
isadmin
isuser
And I want to associate the above login with both isadmin and dbo users.
Is that possible?
Thanks in advance,
Juan Dent, M.Sc.
"Narayana Vyas Kondreddi" wrote:

> Is this the case of an orphanned user, due to moving the db from one domai
n
> to another or something? There is a procedure called sp_change_users_login
,
> but that will not work with Windows accounts.
> Can you simply grant access to that Windows login to SQL Server using
> sp_grantlogin, and add associated user in the database using
> sp_grantdbaccess? You'll have to apply the permissions manually though.
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Juan Dent" <JuanDent@.discussions.microsoft.com> wrote in message
> news:5AF26949-D98D-4F0A-9F6E-2DF92469E88C@.microsoft.com...
> Server 2000 user, but have not found the stored procedure for this.
>
>

Associate User to Login

Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
Gary
This is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (search KB for this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that the
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user would
> keep the same configuration for that user.
> Thank you,
> Gary
>
|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>
|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>

Associate User to Login

Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
GaryThis is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (search KB for this).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that the
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user would
> keep the same configuration for that user.
> Thank you,
> Gary
>|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>

Associate User to Login

Is there a way to associate an existing user in a database to an existing
SQL Server login?
This is SQL Server 2000.
I've restored a database from a backup and when I look at the users, the
users which were there before don't have a Login Name associated with them.
The only way I found to associate it is to delete the user and go to the
login to give that user database access (which automatically creates the
user in the database). If I try to give the user database access with the
old user still in the database, it will give me an error indicating that the
user already exists.
The reason for this is I didn't create the database and the users and was
hoping to associate the user's to the obvious logins to avoid changing any
permissions or roles by accident or omission. Using the existing user would
keep the same configuration for that user.
Thank you,
GaryThis is what sp_change_users_login is for.
Or transfer the logins properly in the first place using sp_help_revlogin (s
earch KB for this).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Gary" <tuffboystl@.hotmail.com> wrote in message news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl..
.
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with them
.
> The only way I found to associate it is to delete the user and go to the
> login to give that user database access (which automatically creates the
> user in the database). If I try to give the user database access with the
> old user still in the database, it will give me an error indicating that t
he
> user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user wou
ld
> keep the same configuration for that user.
> Thank you,
> Gary
>|||You can use sp_change_users_login to change the login/user mapping. See the
Books Online for usage details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>|||HI,
Execute the below command:-
Use dbname
go
sp_change_users_login 'update_one','login_name','User_name'
Thanks
Hari
SQL Server MVP
"Gary" <tuffboystl@.hotmail.com> wrote in message
news:OdGnkRJsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Is there a way to associate an existing user in a database to an existing
> SQL Server login?
> This is SQL Server 2000.
> I've restored a database from a backup and when I look at the users, the
> users which were there before don't have a Login Name associated with
> them. The only way I found to associate it is to delete the user and go to
> the login to give that user database access (which automatically creates
> the user in the database). If I try to give the user database access with
> the old user still in the database, it will give me an error indicating
> that the user already exists.
> The reason for this is I didn't create the database and the users and was
> hoping to associate the user's to the obvious logins to avoid changing any
> permissions or roles by accident or omission. Using the existing user
> would keep the same configuration for that user.
> Thank you,
> Gary
>

Assistance with Stored Procedure

I am running SQL Server 7.0 and using a web interface. I would like
for a user to be able to input multiple values into a single field
with some sort of delimiter (such as a comma). I want to pass this
field into a Stored Procedure and have the stored procedure use the
data to generate the resutls.

Example:

Web page would ask for ID number into a field called IDNum. User
could input one or many ID numbers separated by a comma or some other
delemiter - could even be just a space (113, 114, 145).

SQL statement in Stored Procedure is something like this:

Select * from tblEmployess where IDNumber = @.IDNum

I need the SQL statement to somehow use an "or" or a "loop" to get all
of the numbers passed and use the delimiter to distinguish when the
"loop" stops.

I obtained a module from a friend that allows me to do this in access,
but have recently converted everything to SQL server and web
interface. Now, everyone in the office expects to be able to
accomplish the same results via the web.

Any help is appreciated. If you need any additional information to
provide me some assistance, please email me at
tod.thames@.nc.ngb.army.mil.

Thanks in advance.

TodTake a look at http://www.algonet.se/~sommar/arrays-in-sql.html.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:5ed144f0.0310050454.369f4994@.posting.google.c om...
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
> Example:
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
> SQL statement in Stored Procedure is something like this:
> Select * from tblEmployess where IDNumber = @.IDNum
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@.nc.ngb.army.mil.
> Thanks in advance.
> Tod|||Everytime I try to get to the website you refrenced, I get TCP_ERROR.
Some sort of communication problem. Is there any other sites that have
the same sort of information?

Thanks for the response,

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I don't know if the content is mirrored elsewhere. The author, Erland
Sommarskog, frequents this newsgroup so maybe he'll jump in.

BTW, I don't have any problems accessing the site.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:3f803253$0$195$75868355@.news.frii.net...
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that
have
> the same sort of information?
> Thanks for the response,
> Tod
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||[posted and mailed]

Tod Thames (tod.thames@.nc.ngb.army.mil) writes:
> Everytime I try to get to the website you refrenced, I get TCP_ERROR.
> Some sort of communication problem. Is there any other sites that have
> the same sort of information?

Too bad. If you have the complete error message, I'm interested. I'm
inclined to suspect that this might be some firewall problem at your
side, but I might get carried away of the .mil in your address.

Anyway, here is an excerpt of the part which is most relevant to
you. If you want to read the entire article, just drop me a line.

An Extravagant List-of-integers Procedure

The technique in the previous section can of course be applied to a list
of integers as well, so what comes here is not a true port of the
iter_intlist_to_table function, but a version that goes head over heels
to validate that the list items are valid numbers to avoid a conversion
error. And to be extra ambitious, the procedure permits for signed
numbers such as +98 or -83. If a list item is not a legal number, the
procedure produces a warning. The procedure fills in a temp table that
has a listpos column; this column will show a gap if there is an illegal
item in the input.

CREATE PROCEDURE intlist_to_table_sp @.list ntext AS

DECLARE @.pos int,
@.textpos int,
@.listpos int,
@.chunklen smallint,
@.str nvarchar(4000),
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000)

SET NOCOUNT ON

SELECT @.textpos = 1, @.listpos = 1, @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SELECT @.chunklen = 4000 - datalength(@.leftover) / 2
SELECT @.tmpstr = ltrim(@.leftover + substring(@.list, @.textpos, @.chunklen))
SELECT @.textpos = @.textpos + @.chunklen

SELECT @.pos = charindex(' ', @.tmpstr)
WHILE @.pos > 0
BEGIN
SELECT @.str = rtrim(ltrim(substring(@.tmpstr, 1, @.pos - 1)))
EXEC insert_str_to_number @.str, @.listpos
SELECT @.listpos = @.listpos + 1
SELECT @.tmpstr = ltrim(substring(@.tmpstr, @.pos + 1, len(@.tmpstr)))
SELECT @.pos = charindex(' ', @.tmpstr)
END

SELECT @.leftover = @.tmpstr
END

IF ltrim(rtrim(@.leftover)) <> ''
EXEC insert_str_to_number @.leftover, @.listpos
go

-- This is a sub-procedure to intlist_to_table_sp
CREATE PROCEDURE insert_str_to_number @.str nvarchar(200),
@.listpos int AS

DECLARE @.number int,
@.orgstr nvarchar(200),
@.sign smallint,
@.decimal decimal(10, 0)

SELECT @.orgstr = @.str

IF substring(@.str, 1, 1) IN ('-', '+')
BEGIN
SELECT @.sign = CASE substring(@.str, 1, 1)
WHEN '-' THEN -1
WHEN '+' THEN 1
END
SELECT @.str = substring(@.str, 2, len(@.str))
END
ELSE
SELECT @.sign = 1

IF @.str LIKE '%[0-9]%' AND @.str NOT LIKE '%[^0-9]%'
BEGIN
IF len(@.str) <= 9
SELECT @.number = convert(int, @.str)
ELSE IF len(@.str) = 10
BEGIN
SELECT @.decimal = convert(decimal(10, 0), @.str)
IF @.decimal <= convert(int, 0x7FFFFFFF)
SELECT @.number = @.decimal
END
END

IF @.number IS NOT NULL
INSERT #numbers (listpos, number) VALUES (@.listpos, @.sign * @.number)
ELSE
RAISERROR('Warning: at position %d, the string "%s" is not an legal integer',
10, -1, @.listpos, @.orgstr)
go

Here is how you would use it:

CREATE PROCEDURE get_product_names_iterproc @.ids varchar(50) AS
CREATE TABLE #numbers (listpos int NOT NULL,
number int NOT NULL)
EXEC intlist_to_table_sp @.ids
SELECT P.ProductID, P.ProductName
FROM Northwind..Products P
JOIN #numbers n ON P.ProductID = n.number
go
EXEC get_product_names_iterproc '9 12 27 37'

The validation of the list item is in the sub-procedure
insert_str_to_number. For many purposes it would be sufficient to have
the test

@.str NOT LIKE '%[^0-9]%' AND len(@.str) BETWEEN 1 AND 9

which checks that @.str only contain digits and is at most nine digits
long (that is, you disapprove ten-digit numbers as well as signed
numbers).

You might guess that there is a performance cost for this extravaganza,
and indeed the procedure needs about 50% more time than the corresponding
function. Still, for many situations, the execution time is acceptable.

One note about the warning produced with RAISERROR: with ADO, this
warning may be difficult or impossible to detect on client level. If you
change the severity from 10 to 11, it will be an error, and raise an
error in your client code.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Thanks for the response - it's a little above my abilities, but I plan
on studying it and trying to make it work for me project.

Tod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See if the following link helps..
http://tinyurl.com/6iil

--
-- Anith|||Tod,

Try this:

create procedure ListEmployees
@.IDNum char(1024)
as begin
set @.IDNum = ' ' + replace(@.IDNum, ',', ' ') + ' '
select *
from tblEmployess
where @.IDNum like ('% ' + ltrim(str(IDNumber)) + ' %')
end

Shervin

"Tod Thames" <tod.thames@.nc.ngb.army.mil> wrote in message
news:5ed144f0.0310050454.369f4994@.posting.google.c om...
> I am running SQL Server 7.0 and using a web interface. I would like
> for a user to be able to input multiple values into a single field
> with some sort of delimiter (such as a comma). I want to pass this
> field into a Stored Procedure and have the stored procedure use the
> data to generate the resutls.
> Example:
> Web page would ask for ID number into a field called IDNum. User
> could input one or many ID numbers separated by a comma or some other
> delemiter - could even be just a space (113, 114, 145).
> SQL statement in Stored Procedure is something like this:
> Select * from tblEmployess where IDNumber = @.IDNum
>
> I need the SQL statement to somehow use an "or" or a "loop" to get all
> of the numbers passed and use the delimiter to distinguish when the
> "loop" stops.
> I obtained a module from a friend that allows me to do this in access,
> but have recently converted everything to SQL server and web
> interface. Now, everyone in the office expects to be able to
> accomplish the same results via the web.
> Any help is appreciated. If you need any additional information to
> provide me some assistance, please email me at
> tod.thames@.nc.ngb.army.mil.
> Thanks in advance.
> Tod

assigning user to a database programmatically

Environment : vc.net , SQL Server
Hi All,
I have programmatically created an SQL Server database
by executing the script file for it. I have logged in as administrator
for creating the above database in my vc.net code using SQL APIs.
Now i need to assign a new user and pasword to this database
i have created in my program. Is there a way to do it ?
Regards,
Asif
Use sp_addlogin for adding the login to the database, then use sp_grantdbaccess to grant your user access to the database. If you then want to add the user to a role use sp_addrolemember
hth,
Lance

assigning user to a database programmatically

Environment : vc.net , SQL Server
Hi All,
I have programmatically created an SQL Server database
by executing the script file for it. I have logged in as administrator
for creating the above database in my vc.net code using SQL APIs.
Now i need to assign a new user and pasword to this database
i have created in my program. Is there a way to do it ?
Regards,
AsifUse sp_addlogin for adding the login to the database, then use sp_grantdbacc
ess to grant your user access to the database. If you then want to add the
user to a role use sp_addrolemember
hth,
Lance

Assigning User to a Database

Hi

I am using SQL SERVER 2000. Until now my application used the default user "sa", but now the illigal access to my database make me move to a more secure login.

i am new to this concept.

i need to create a login, which i am successful in creating, but my problem is

I need to allow only this user to access my database and no other user should login my database.

please can any one explain how to do this.

its very urgent.

regards

James Alvin

Noone is granted access to a database unless you allow it, so go ahead and create a user and give him the appropiate permissions on the database. Make in addition sure, that if you want to restrict the sysadmin users (which is by default the sa and the members of the sysadmin group, e.g. the local administrators) you will have to remove them from the groups / disable the sa account.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

hi

how can we disable sa account in sql server 2000

|||

You can't disable sa on a SQL Server 2000 instance. Not in any way that would be supported. Some time ago, a few people hacked at the system tables and eventually removed sa but then they had continual problems, couldn't apply service packs and were on an unsupported system. So not in any way that would be supported or stable.

Another option, depending on your application, would be to use just Windows Authentication and use impersonation in your application to log in with just the one windows account you add to the users for this database.

Sysadmins will still be able to access that database though. If it's that critical to lock out everyone, you would want to look at auditing as well as explore third party options for encryption.

-Sue

.

|||

Yes, I ment dismanteling instead of disabling. What I do in reality is to give the sa a cryptic (long and non-guessable) password and lock this in the (virtual) safe. Noone should use that account beside emergencies (like locked accounts etc.) Then I create a new login which has the same rights but a non well-known name (like sa, everyone know that this is the system adminstrator and therefore you only have to guess the password as you already know the name). This account is then mainly used for tasks that cannot be done with the Windows authentication (like users which are not present in the AD). If you are able only use Windows authentication you can even discard this task.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Assigning User / Group thru Web service?

I can't find a way to set user / group to a role thru the web service, this
functionality is availible in the Reports interface, but not thru the web
service?
Just to note i'v implemented Custom Security, agains a users data store. We
need to bulk load 1500 users.
Can this be done thru the db? that would be a solution also if the web
methods are not availible?
sql server 2000, rs 1.0Report Manager uses SetPolicies and SetSystemPolicies SOAP methods.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nathan Myers" <Nathan Myers@.discussions.microsoft.com> wrote in message
news:69021F82-9C4B-4EE7-896D-A1C3E3C9B20B@.microsoft.com...
>I can't find a way to set user / group to a role thru the web service, this
> functionality is availible in the Reports interface, but not thru the web
> service?
> Just to note i'v implemented Custom Security, agains a users data store.
> We
> need to bulk load 1500 users.
> Can this be done thru the db? that would be a solution also if the web
> methods are not availible?
> sql server 2000, rs 1.0|||do these methods inclue seting a Role to a User / Group. I haven't seen it
in the documentation anywere. Can you show an example of setting a Role
(System Adminstrator) to a User (Nathan.Myers) via the Web services
SetPolicies or SetSystemPolicies?
"Lev Semenets [MSFT]" wrote:
> Report Manager uses SetPolicies and SetSystemPolicies SOAP methods.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Nathan Myers" <Nathan Myers@.discussions.microsoft.com> wrote in message
> news:69021F82-9C4B-4EE7-896D-A1C3E3C9B20B@.microsoft.com...
> >I can't find a way to set user / group to a role thru the web service, this
> > functionality is availible in the Reports interface, but not thru the web
> > service?
> >
> > Just to note i'v implemented Custom Security, agains a users data store.
> > We
> > need to bulk load 1500 users.
> >
> > Can this be done thru the db? that would be a solution also if the web
> > methods are not availible?
> >
> > sql server 2000, rs 1.0
>
>|||Use SetSystemPolicies to set System Administrator role for a user.
Basically you need to get system policies using GetSystemPolicies, add
policy for user, and then call SetSystemPolicies
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nathan Myers" <NathanMyers@.discussions.microsoft.com> wrote in message
news:86EAE013-7C63-40AE-997C-CE263A8C58BA@.microsoft.com...
> do these methods inclue seting a Role to a User / Group. I haven't seen it
> in the documentation anywere. Can you show an example of setting a Role
> (System Adminstrator) to a User (Nathan.Myers) via the Web services
> SetPolicies or SetSystemPolicies?
> "Lev Semenets [MSFT]" wrote:
>> Report Manager uses SetPolicies and SetSystemPolicies SOAP methods.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Nathan Myers" <Nathan Myers@.discussions.microsoft.com> wrote in message
>> news:69021F82-9C4B-4EE7-896D-A1C3E3C9B20B@.microsoft.com...
>> >I can't find a way to set user / group to a role thru the web service,
>> >this
>> > functionality is availible in the Reports interface, but not thru the
>> > web
>> > service?
>> >
>> > Just to note i'v implemented Custom Security, agains a users data
>> > store.
>> > We
>> > need to bulk load 1500 users.
>> >
>> > Can this be done thru the db? that would be a solution also if the web
>> > methods are not availible?
>> >
>> > sql server 2000, rs 1.0
>>

Assigning Roles to Roles

I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.

When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.

Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.I have to test it, but I find it hard to believe...

Why do you want to do this though?

Assigning Permissions to a Database

I'm trying to grant a user full access to a database.
Although I've given this user the role of db_owner, he still doesnt have the
same access as ,for example, the "dbo" user.
When I look at the table permissions, the "dbo" user has everything selected
while my user has nothing selected. Do I have to go into each table and
manually assign these permissions or can I somhow apply all of the
permissions using the database roles?
Thanks!Db_owner role members and the 'dbo' user hare full permissions over all
objects in the database. There is no need to grant object permissions
because object permissions are not checked for db_owner role members.
Hope this helps.
Dan Guzman
SQL Server MVP
"newbie" <newbie@.sql.com> wrote in message
news:eI8ZEZ7ZGHA.4788@.TK2MSFTNGP02.phx.gbl...
> I'm trying to grant a user full access to a database.
> Although I've given this user the role of db_owner, he still doesnt have
> the same access as ,for example, the "dbo" user.
> When I look at the table permissions, the "dbo" user has everything
> selected while my user has nothing selected. Do I have to go into each
> table and manually assign these permissions or can I somhow apply all of
> the permissions using the database roles?
>
> Thanks!
>|||Besides, if a user belongs to db_owner role, he isn't dbo user. For example,
if that user creates a table, this table will be created as user.tabla but
if the dbo user creates a table, this table will be created as dbo.tabla .
Then, the user should create the table hardcoding the dbo.tabla
Mpia
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23W$9p9$ZGHA.5088@.TK2MSFTNGP03.phx.gbl...
> Db_owner role members and the 'dbo' user hare full permissions over all
> objects in the database. There is no need to grant object permissions
> because object permissions are not checked for db_owner role members.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "newbie" <newbie@.sql.com> wrote in message
> news:eI8ZEZ7ZGHA.4788@.TK2MSFTNGP02.phx.gbl...
>

Assigning permissions

Hello,
I have the following:
schema: xproject
Database Role: WebPublisher
User: MyUserHandle
sproc: xproject.MyProcedure
I added the role "WebPublisher" to the schema "xproject" with
Delete
Execute
Insert
Select
Update
I added the user "MyUserHandle" to the database role "WebPublisher"
When I tried to execute the stored procedure from an ASP.NET page, I got the
error
EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
schema 'xproject'
However, when I added the user to the schema and gave it the same
permissions as the database role, the page rendered.
My question is why do I have to assign the user to the schema when the
database role already has the user?
Thanks, sck10Hi
"sck10" wrote:
> Hello,
> I have the following:
> schema: xproject
> Database Role: WebPublisher
> User: MyUserHandle
> sproc: xproject.MyProcedure
> I added the role "WebPublisher" to the schema "xproject" with
> Delete
> Execute
> Insert
> Select
> Update
> I added the user "MyUserHandle" to the database role "WebPublisher"
>
> When I tried to execute the stored procedure from an ASP.NET page, I got the
> error
> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
> schema 'xproject'
This would imply that the user is not actually a member of the role. See
below:
> However, when I added the user to the schema and gave it the same
> permissions as the database role, the page rendered.
I am not sure what you mean by adding the "user to the schema", granting the
permission to execute the procedure to the user will allow you to execute the
procedure. See below:
> My question is why do I have to assign the user to the schema when the
> database role already has the user?
No, see below:
> Thanks, sck10
>
SELECT @.@.VERSION
GO
/*
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
*/
CREATE DATABASE TestPermissions
GO
USE TestPermissions
GO
CREATE SCHEMA xproject
GO
CREATE ROLE WebPublisher
GO
GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
WebPublisher
GO
CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
varchar(10) not null )
GO
CREATE Procedure xproject.MyProcedure
AS
SELECT id, valuetext FROM mytable
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
CREATE USER MyUserHandle WITHOUT LOGIN
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername ='MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername ='MyUserHandle '
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
The EXECUTE permission was denied on the object 'MyProcedure', database
'TestPermissions', schema 'xproject'.
*/
REVERT
GO
GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
GO
EXECUTE AS USER = 'MyUserHandle'
GO
EXEC xproject.MyProcedure
GO
/*
id valuetext
-- --
(0 row(s) affected)
*/
REVERT
GO
USE MASTER
GO
DROP DATABASE TestPermissions
GO
John|||thanks for the example John,
this helps greatly.
sck10
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AB4CE95C-EF32-4431-9D6B-B4872796A1FF@.microsoft.com...
> Hi
> "sck10" wrote:
>> Hello,
>> I have the following:
>> schema: xproject
>> Database Role: WebPublisher
>> User: MyUserHandle
>> sproc: xproject.MyProcedure
>> I added the role "WebPublisher" to the schema "xproject" with
>> Delete
>> Execute
>> Insert
>> Select
>> Update
>> I added the user "MyUserHandle" to the database role "WebPublisher"
>>
>> When I tried to execute the stored procedure from an ASP.NET page, I got
>> the
>> error
>> EXECUTE permission denied on object 'MyProcedure', database 'mydatabase',
>> schema 'xproject'
> This would imply that the user is not actually a member of the role. See
> below:
>> However, when I added the user to the schema and gave it the same
>> permissions as the database role, the page rendered.
> I am not sure what you mean by adding the "user to the schema", granting
> the
> permission to execute the procedure to the user will allow you to execute
> the
> procedure. See below:
>> My question is why do I have to assign the user to the schema when the
>> database role already has the user?
> No, see below:
>> Thanks, sck10
> SELECT @.@.VERSION
> GO
> /*
> Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)
> Mar 23 2007 16:28:52
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> */
> CREATE DATABASE TestPermissions
> GO
> USE TestPermissions
> GO
> CREATE SCHEMA xproject
> GO
> CREATE ROLE WebPublisher
> GO
> GRANT Delete, Execute, Insert, Select, Update ON SCHEMA :: xproject TO
> WebPublisher
> GO
> CREATE TABLE xproject.MyTable ( id int not null identity, valuetext
> varchar(10) not null )
> GO
> CREATE Procedure xproject.MyProcedure
> AS
> SELECT id, valuetext FROM mytable
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> CREATE USER MyUserHandle WITHOUT LOGIN
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> EXEC sp_addrolemember @.rolename = 'WebPublisher', @.membername => 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> EXEC sp_droprolemember @.rolename = 'WebPublisher', @.membername => 'MyUserHandle '
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> Msg 229, Level 14, State 5, Procedure MyProcedure, Line 1
> The EXECUTE permission was denied on the object 'MyProcedure', database
> 'TestPermissions', schema 'xproject'.
> */
> REVERT
> GO
> GRANT EXECUTE ON xproject.MyProcedure TO MyUserHandle
> GO
> EXECUTE AS USER = 'MyUserHandle'
> GO
> EXEC xproject.MyProcedure
> GO
> /*
> id valuetext
> -- --
> (0 row(s) affected)
> */
> REVERT
> GO
> USE MASTER
> GO
> DROP DATABASE TestPermissions
> GO
> John
>

assigning DB-user to server role

Hello NG,
each time we assign users to roles, we have to additionally grant each
access right of each function of each table manually to this new user. Of
course we'd expect this to be done by the assignment itself.
Either we don't understand the servers's security philosophy correctly or
there should be another procedure to grant users access to the database via
the role, which has already defined access rights.
Thankful for any hint ...
MarcYou should be able to grant object permissions to the roles only. Member
users will inherit permissions via role membership. If a user is a member
of multiple roles, granted permissions are cumulative. However, denied
permissions take precedence.
I'm not sure how you've setup your users but the script below illustrates
this technique.
USE MyDatabase
--setup role security
EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyTable TO MyRole
--setup user security
EXEC sp_grantlogin 'MyDomain\MyUser'
EXEC sp_grantdbaccess 'MyDomain\MyUser'
EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40164c5c$1@.si-nic.hrz.uni-siegen.de...

quote:

---

> Hello NG,
> each time we assign users to roles, we have to additionally grant each
> access right of each function of each table manually to this new user. Of
> course we'd expect this to be done by the assignment itself.
> Either we don't understand the servers's security philosophy correctly or
> there should be another procedure to grant users access to the database

---

via

quote:

---

> the role, which has already defined access rights.
> Thankful for any hint ...
> Marc
>

---

|||You script seems to promise what we expected when we used the Enterprise
Manager to assign users to roles. Why does it work through scripts and not
through "klick-it"?
Marc
"Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...

quote:

---

> You should be able to grant object permissions to the roles only. Member
> users will inherit permissions via role membership. If a user is a member
> of multiple roles, granted permissions are cumulative. However, denied
> permissions take precedence.
> I'm not sure how you've setup your users but the script below illustrates
> this technique.
> USE MyDatabase
> --setup role security
> EXEC sp_addrole 'MyRole'
> GRANT SELECT ON MyTable TO MyRole
> --setup user security
> EXEC sp_grantlogin 'MyDomain\MyUser'
> EXEC sp_grantdbaccess 'MyDomain\MyUser'
> EXEC sp_addrolemember 'MyRole', 'MyDomain\MyUser'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
> news:40164c5c$1@.si-nic.hrz.uni-siegen.de...
Of[QUOTE]
or[QUOTE]
> via
>

---

|||You should be able to do these same steps using EM as well. I find that SQL
scripts are a lot easier to post here that the GUI steps. My personal
preference is to use scripts for most tasks since these can be easily
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"M.Staiger" <mstaiger@.informatik.uni-siegen.de> wrote in message
news:40167e62$1@.si-nic.hrz.uni-siegen.de...

quote:

---

> You script seems to promise what we expected when we used the Enterprise
> Manager to assign users to roles. Why does it work through scripts and not
> through "klick-it"?
> Marc
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> schrieb im Newsbeitrag
> news:eK1m53N5DHA.2692@.TK2MSFTNGP09.phx.gbl...
Member[QUOTE]
member[QUOTE]
illustrates[QUOTE]
> Of
> or
database[QUOTE]
>

---

|||Perhaps if you used profiler while you recreate your scenario in EM (create
the roles, grant permissions to the role, add users to role, try to execute
your query as that user) then posted the tsql scripts that would help us
figure out what's going on.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Assigning a unique ID

I have an asp page in which the user completes some information and the data
is stored in an SQL database. I want to automatically assign an unique ID
number (can start with 1) to each record when the data is saved to the
database and display this number on the confirmation page.
This is probably much easier that I am making it. Any help would be
appreciated.
Thanks,CREATE TABLE dbo.CustomerData
(
CustomerDataID INT IDENTITY(1,1),
SomeData VARCHAR(32)
)
GO
CREATE PROCEDURE dbo.AddCustomerData
@.SomeData VARCHAR(32),
@.CustomerDataID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.CustomerData(SomeData) SELECT @.SomeData;
SET @.CustomerDataID = SCOPE_IDENTITY();
END
GO
DECLARE @.id INT;
EXEC dbo.AddCustomerData 'foo', @.id OUTPUT;
SELECT new_id = @.id;
SELECT CustomerDataID, SomeData FROM dbo.CustomerData;
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:DDAB88EE-9D62-499F-9D02-BFA99948B222@.microsoft.com...
>I have an asp page in which the user completes some information and the
>data
> is stored in an SQL database. I want to automatically assign an unique ID
> number (can start with 1) to each record when the data is saved to the
> database and display this number on the confirmation page.
> This is probably much easier that I am making it. Any help would be
> appreciated.
> Thanks,|||Do I drop this code on the asp page?
"Aaron Bertrand [SQL Server MVP]" wrote:

> CREATE TABLE dbo.CustomerData
> (
> CustomerDataID INT IDENTITY(1,1),
> SomeData VARCHAR(32)
> )
> GO
> CREATE PROCEDURE dbo.AddCustomerData
> @.SomeData VARCHAR(32),
> @.CustomerDataID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.CustomerData(SomeData) SELECT @.SomeData;
> SET @.CustomerDataID = SCOPE_IDENTITY();
> END
> GO
> DECLARE @.id INT;
> EXEC dbo.AddCustomerData 'foo', @.id OUTPUT;
> SELECT new_id = @.id;
> SELECT CustomerDataID, SomeData FROM dbo.CustomerData;
>
>
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:DDAB88EE-9D62-499F-9D02-BFA99948B222@.microsoft.com...
>
>|||No, this is T-SQL code, not ASP code. For some help running the stored
procedure from ASP, see an ASP newsgroup, if these articles don't clear it
up:
http://www.aspfaq.com/2201
http://www.aspfaq.com/params.htm
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:A33184A6-09AF-4CF8-AB13-44009E40E7CB@.microsoft.com...
> Do I drop this code on the asp page?|||Gasp! No, you copy / paste it into the OnBlur event your website's flaming
logo. Please tell me this is not a e-commerce, financial, or defense related
website!
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:A33184A6-09AF-4CF8-AB13-44009E40E7CB@.microsoft.com...
> Do I drop this code on the asp page?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Are you kidding. It is just an company Intranet page.
Man, if they let me program fo rthe feds, look out...lol
Just trying to automate a process.
Let me ask this on the example from Aaron.
I get that dbo.CustomerData is my DB name (Compliance). My ID field is
called RequestID so is that CustomerDataID or SomeData? What would the othe
r
one be (the name of the table?).
Sorry, not a programmer by trade but I certainly appreciate the help...
"JT" wrote:

> Gasp! No, you copy / paste it into the OnBlur event your website's flaming
> logo. Please tell me this is not a e-commerce, financial, or defense relat
ed
> website!
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:A33184A6-09AF-4CF8-AB13-44009E40E7CB@.microsoft.com...
>
>|||OK, it takes me awhile to grasp the concept but here is what I got:
CREATE TABLE dbo.CommunityService
(
RefNum Int IDENTITY(1,1),
BranchDept VARCHAR(75),
Region VARCHAR(50),
EmployeeName VARCHAR(50),
CompletedBy VARCHAR(50),
Organization VARCHAR(125),
Contacts VARCHAR(50),
Phone VARCHAR(50),
Address VARCHAR(255),
CensusArea NTEXT,
ServiceType NTEXT,
ServiceDesc NTEXT,
BenefitDesc NTEXT,
MoneyEquip NTEXT,
Comments NTEXT,
TimeStamp DATETIME
)
GO
CREATE PROCEDURE dbo.AddRequestID
@.Region VARCHAR(50),
@.RefNum INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.CommunityService(Region) SELECT @.Region;
SET @.RefNum = SCOPE_IDENTITY();
END
GO
DECLARE @.id INT;
EXEC dbo.AddRequestID, @.id OUTPUT;
SELECT new_id = @.id;
SELECT RefNum, Region FROM dbo.CommunityService;
My question is that I am getting an error on Line 3 near ','. It all seems
OK so what am I missing?
********************************
"Ken D." wrote:
> Are you kidding. It is just an company Intranet page.
> Man, if they let me program fo rthe feds, look out...lol
> Just trying to automate a process.
> Let me ask this on the example from Aaron.
> I get that dbo.CustomerData is my DB name (Compliance). My ID field is
> called RequestID so is that CustomerDataID or SomeData? What would the ot
her
> one be (the name of the table?).
> Sorry, not a programmer by trade but I certainly appreciate the help...
> "JT" wrote:
>|||> My question is that I am getting an error on Line 3 near ','. It all
> seems
> OK so what am I missing?
Part of the code sample I sent. What does this mean?

> EXEC dbo.AddRequestID, @.id OUTPUT;
You forgot to include your region parameter:
EXEC dbo.AddRequestID 'NorthEast', @.id OUTPUT;|||Cool. I added 'test" and changed my seed to 0. It worked wonderful.
Thanks and so sorry for the ignorance.
Have a great day Aaron.
"Aaron Bertrand [SQL Server MVP]" wrote:

>
> Part of the code sample I sent. What does this mean?
>
> You forgot to include your region parameter:
> EXEC dbo.AddRequestID 'NorthEast', @.id OUTPUT;
>
>
>
>|||Aaron,
One last question. What is I wanted to add a prefix to the ID. Is that
possible?
"Aaron Bertrand [SQL Server MVP]" wrote:

> CREATE TABLE dbo.CustomerData
> (
> CustomerDataID INT IDENTITY(1,1),
> SomeData VARCHAR(32)
> )
> GO
> CREATE PROCEDURE dbo.AddCustomerData
> @.SomeData VARCHAR(32),
> @.CustomerDataID INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT dbo.CustomerData(SomeData) SELECT @.SomeData;
> SET @.CustomerDataID = SCOPE_IDENTITY();
> END
> GO
> DECLARE @.id INT;
> EXEC dbo.AddCustomerData 'foo', @.id OUTPUT;
> SELECT new_id = @.id;
> SELECT CustomerDataID, SomeData FROM dbo.CustomerData;
>
>
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:DDAB88EE-9D62-499F-9D02-BFA99948B222@.microsoft.com...
>
>

assign truncate rights to a user

i have a user who has delete rights on a table, but when i call truncate
table statement it says not enough permission.
how to assign truncate rightsIn 2000, you can't grant this. You need to be table owner or higher. In 2005
, you have some options.
From 2005 Books Online, TRUNCATE TABLE:
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permi
ssions default to the
table owner, members of the symin fixed server role, and the db_owner and
db_ddladmin fixed
database roles, and are not transferable. However, you can incorporate the T
RUNCATE TABLE statement
within a module, such as a stored procedure, and grant appropriate permissio
ns to the module using
the EXECUTE AS clause. For more information, see Using EXECUTE AS to Create
Custom Permission Sets.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vikram" <aa@.aa> wrote in message news:u2lvh83FGHA.1288@.TK2MSFTNGP09.phx.gbl...ed">
>i have a user who has delete rights on a table, but when i call truncate
> table statement it says not enough permission.
> how to assign truncate rights
>|||BOL says:
Permissions
TRUNCATE TABLE permissions default to the table owner, members of the
symin fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable.
"Vikram" <aa@.aa> wrote in message
news:u2lvh83FGHA.1288@.TK2MSFTNGP09.phx.gbl...
>i have a user who has delete rights on a table, but when i call truncate
> table statement it says not enough permission.
> how to assign truncate rights
>

Assign several different sets of paramters, to be chosen by user?

Hi,

Is there a way to put in several different sets of values for the defaults in parameters, where the user could then choose which set of defaults they want?

For example, I have two drop-downs for dates: Start Date and End Date. I would also like another drop-down (or button, or whatever) called, say "Interval". It would have choices such as "1 week interval" or "52 week interval", and whatever is chosen would set the Start and End Dates to whatever default values I had assigned to the parameters in "Interval".

It's not quite the same as cascading parameters. Any suggestions would be greatly appreciated! Thanks.Sorry, but this is cascading parameters. One parameter which pulls of the data from the server and fills the other parameters afterwards, thats truly cascading.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

assign role of SQLAgentOperatorRole

Hi there,

How can I assign a user role of SQLAgentOperatorRole.

I tried with sp_addsrvrolemember but failed.

Thanks

Rahul

This should be a DB-scoped role, not a server-scoped role. You should use sp_addrolemember (http://msdn2.microsoft.com/en-us/library/ms187750(SQL.90).aspx) instead.

Please refer to SQL Server Agent BOL for further details and let us know if you have further questions

-Raul Garcia

SDE/T

SQL Server Engine

|||

Cant get it through.

I always thought Agent roles should be Server level role.

Any way : sp_addrolemember SQLAgentOperatorRole, @.username didnt work either.

thanks

Rahul

|||

Agenet principals are scoped to msdb; make sure you are running the statement from msdb DB. For example:

use [msdb]

go

-- The code where @.userName is declared & set

-- ...

EXEC sp_addrolemember 'SQLAgentOperatorRole', @.userName

go

If you still have problems let us know, and please include the error number and message in order to help us understand the nature of the fauilure.

-Raul Garcia

SDE/T

SQL Server Engine

Assign permissions to multiple objects

Yes
1. Simply add user to a group with enough perms to do it.
or
2. use sp_MSForeachtable or a cursor and loop through the objects assigning
perms as appriopriate by generating dynamic sql.
or
3. Script out perms from prod and apply to dev for login in question.
"rsbaier@.gmail.com" wrote:

> I know I should know the answer to this question, but unfortunately I
> do not.
> We have a SQL Server 7.0 production database (will be upgraded soon).
> I am attempting to restore this database in my development environment
> which is running SQL Server 2005 developer edition. The restore
> appeared to succeed without problem, but one of the logins ended up not
> getting any of the correct permissions assigned to any objects for some
> reason. Is there any way to easily assign permissions to multiple
> objects for a single login? If I have to do it manually and
> individually it will take hours.
> Thanks in advance for any advice.
>I know I should know the answer to this question, but unfortunately I
do not.
We have a SQL Server 7.0 production database (will be upgraded soon).
I am attempting to restore this database in my development environment
which is running SQL Server 2005 developer edition. The restore
appeared to succeed without problem, but one of the logins ended up not
getting any of the correct permissions assigned to any objects for some
reason. Is there any way to easily assign permissions to multiple
objects for a single login? If I have to do it manually and
individually it will take hours.
Thanks in advance for any advice.|||Yes
1. Simply add user to a group with enough perms to do it.
or
2. use sp_MSForeachtable or a cursor and loop through the objects assigning
perms as appriopriate by generating dynamic sql.
or
3. Script out perms from prod and apply to dev for login in question.
"rsbaier@.gmail.com" wrote:

> I know I should know the answer to this question, but unfortunately I
> do not.
> We have a SQL Server 7.0 production database (will be upgraded soon).
> I am attempting to restore this database in my development environment
> which is running SQL Server 2005 developer edition. The restore
> appeared to succeed without problem, but one of the logins ended up not
> getting any of the correct permissions assigned to any objects for some
> reason. Is there any way to easily assign permissions to multiple
> objects for a single login? If I have to do it manually and
> individually it will take hours.
> Thanks in advance for any advice.
>