Alternate names

I need to implement support for alternate names on the database Im working on.
When a user looks up: Antony Bigglesworth the query would return both: Antony Bigglesworth as well as Tony Bigglesworth, would both exist.
Anyone has a best practice on this or has done this before?You mean hat it returns every first name that is a substring of the name you look for?
Something like:

Where SearchString Like '%' + FirstNameField + '%'

I believe that would return, Antony, Tony, Ton, A, but not Abby and George.|||I presume iot is more complex that that yes? "Dick" for "Richard" and "Teddy" for "Edward"?|||I presume iot is more complex that that yes? "Dick" for "Richard" and "Teddy" for "Edward"?

Yes, Antony clearly wasnt the best choice.

I need Richard Bigglesworth to be returned when I look for Dick Bigglesworth!

Thanks for getting back on this.|||Use my fuzzy-search algorithm. It was created to do exactly what you describe.
http://sqlblindman.googlepages.com/fuzzysearchalgorithm

Example:
set nocount on
select dbo.comparetext('Antony Bigglesworth', 'Tony Bigglesworth') as CompareValue1
select dbo.comparetext('Richard Bigglesworth', 'Dick Bigglesworth') as CompareValue2

Results:
CompareValue1
----
94

CompareValue2
----
74|||Thanks Blindman,

That looks great.

I also have to define a group of names which would be specificly checked and collected then returned from the database. Thus, the user would look for "Tony" as the first name, and so all the people with first name "Tony" and all the people with the first name "Anthony" would be returned.

I kind of did it now, created a table where you can add in these name groups, and a small sql script then checks if the sought after first name is in the table, if it is it gets the group id, then cycles through the group collecting all the relevant records then returns them.

Thanks for all the help on this|||To be thorough, you should accept the names "Buddy" and "Pal" and perhaps "Dude" as wild-cards that match any name.|||don't forget "Todd" and "Biff"|||In SoCal, we also have to expand a bit on Blindman's suggestion, since we also have "Duuuuuude!" as well as any name with the suffix "meister" appended (as in "Paulmeister" or "Blindmanmeister").sql

Alter UserDefined Datatype

How can I alter a userdefined datatype.

eg: A user defined datatype of Dt_Quantity numeric(16,0)

Want to change it to numeric(16,3).

Is it possible?

If it requires a system catelog updation please help.

This requirment is came after the implementation of the DataBase.

Please help me fast|||which version of sql server are you using ?|||Using the SQL Server 2005|||

you have to drop it and recreate...

From BOL :

Note: User-defined types cannot be modified after they are created, because changes could invalidate data in the tables or indexes. To modify a type, you must either drop the type and then re-create it, or issue an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause. For more information, see ALTER ASSEMBLY (Transact-SQL).

Madhu

ALTER USER WITH LOGIN

What's the syntax to use the ALTER USER command to remap an orphaned user to
an existing login in SS2005?It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/...y/ms174378.aspx
Ekrem ?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.comsql

ALTER USER WITH LOGIN

What's the syntax to use the ALTER USER command to remap an orphaned user to
an existing login in SS2005?It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/library/ms174378.aspx
--
Ekrem Ã?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.com|||Yup, I already know that but Ken did not mention this need. I did not need
to mentioned this catch because it's already declared in the link I gave.
Alternative is alternative.
--
Ekrem Önsoy
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:15E92A6A-2E39-40B8-A9F9-0F90007BB3D7@.microsoft.com...
> The only catch is it that sp_change_users_login works only for SQL Server
> logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
> logins.
> Plamen Ratchev
> http://www.SQLStudio.com|||That worked fine on my development machine, but on the server I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'psts_web'.
Here's the code I tried to run:
ALTER USER psts_web WITH LOGIN psts_web
"psts_web" is the user name and the login name and they both exist in the db.
Thanks
/Ken|||Hi Ken,
You used incorrect syntax. It should be:
ALTER USER psts_web WITH LOGIN = psts_web
Note the part "... LOGIN = psts_web", you were missing the "=".
HTH,
Plamen Ratchev
http://www.SQLStudio.com

ALTER USER WITH LOGIN

What's the syntax to use the ALTER USER command to remap an orphaned user to
an existing login in SS2005?
It should be:
ALTER USER user_name WITH LOGIN = login_name
Replace the <user_name> and <login_name> accordingly with the orphaned user
and the existing login.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Do you know about the "sp_change_users_login" sp?
You can check it out from the following link if you don't.
http://technet.microsoft.com/en-us/library/ms174378.aspx
Ekrem ?nsoy
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:8C6629E3-C7D7-467A-A54E-0D9DC5F480F9@.microsoft.com...
> What's the syntax to use the ALTER USER command to remap an orphaned user
> to
> an existing login in SS2005?
|||The only catch is it that sp_change_users_login works only for SQL Server
logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
logins.
Plamen Ratchev
http://www.SQLStudio.com
|||Yup, I already know that but Ken did not mention this need. I did not need
to mentioned this catch because it's already declared in the link I gave.
Alternative is alternative.
Ekrem nsoy
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:15E92A6A-2E39-40B8-A9F9-0F90007BB3D7@.microsoft.com...
> The only catch is it that sp_change_users_login works only for SQL Server
> logins, while ALTER USER WITH LOGIN supports both SQL Server and Windows
> logins.
> Plamen Ratchev
> http://www.SQLStudio.com
|||That worked fine on my development machine, but on the server I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'psts_web'.
Here's the code I tried to run:
ALTER USER psts_web WITH LOGIN psts_web
"psts_web" is the user name and the login name and they both exist in the db.
Thanks
/Ken
|||Hi Ken,
You used incorrect syntax. It should be:
ALTER USER psts_web WITH LOGIN = psts_web
Note the part "... LOGIN = psts_web", you were missing the "=".
HTH,
Plamen Ratchev
http://www.SQLStudio.com

alter user defined data type length

I have one UDD, which is used in some tables.
Now i want to change its length. How can i do that ?
The only way i know of is to drop it and re-create it...
sp_droptype
sp_addtype
but you can't drop a type if it's being referenced by any columns
The way to go would be this:
EXEC sp_rename UDName, old_UDName
EXEC sp_addtype UDName, 'varchar(20)'
ALTER TABLE tbl1 ALTER COLUMN col1 UDName
ALTER TABLE tbl2 ALTER COLUMN col2 UDName
HTH. Ryan
"Vikram" <aa@.aa> wrote in message
news:e7qkGn9KGHA.2012@.TK2MSFTNGP14.phx.gbl...
>I have one UDD, which is used in some tables.
> Now i want to change its length. How can i do that ?
>

alter user defined data type length

I have one UDD, which is used in some tables.
Now i want to change its length. How can i do that ?The only way i know of is to drop it and re-create it...
sp_droptype
sp_addtype
but you can't drop a type if it's being referenced by any columns
The way to go would be this:
EXEC sp_rename UDName, old_UDName
EXEC sp_addtype UDName, 'varchar(20)'
ALTER TABLE tbl1 ALTER COLUMN col1 UDName
ALTER TABLE tbl2 ALTER COLUMN col2 UDName
HTH. Ryan
"Vikram" <aa@.aa> wrote in message
news:e7qkGn9KGHA.2012@.TK2MSFTNGP14.phx.gbl...
>I have one UDD, which is used in some tables.
> Now i want to change its length. How can i do that ?
>

alter user defined data type length

I have one UDD, which is used in some tables.
Now i want to change its length. How can i do that ?The only way i know of is to drop it and re-create it...
sp_droptype
sp_addtype
but you can't drop a type if it's being referenced by any columns
The way to go would be this:
EXEC sp_rename UDName, old_UDName
EXEC sp_addtype UDName, 'varchar(20)'
ALTER TABLE tbl1 ALTER COLUMN col1 UDName
ALTER TABLE tbl2 ALTER COLUMN col2 UDName
HTH. Ryan
"Vikram" <aa@.aa> wrote in message
news:e7qkGn9KGHA.2012@.TK2MSFTNGP14.phx.gbl...
>I have one UDD, which is used in some tables.
> Now i want to change its length. How can i do that ?
>sql

Alter table permission to dbo

I have the following requirement

I am creating a login and database user 'test' on a database with dbo
role .
I want to remove create table , alter table permisions to this user.
I am able to revoke create table permission but alter table goes
through.
I gave a command deny insert,delete,update on ssycolumns to test.
Still I am not able to prevent user altering schema . Alter table
successfully goes throgh.

I do not want to use datreader and datwriter role.
since I want user 'test' to create storred procedure with dbo owner

Is there a way to achieve this ?

Thanks

M A Srinivas"M A Srinivas" <masri@.vsnl.com> wrote in message
news:f7e90f78.0309260634.3791a935@.posting.google.c om...
> I have the following requirement
> I am creating a login and database user 'test' on a database with dbo
> role .
> I want to remove create table , alter table permisions to this user.
> I am able to revoke create table permission but alter table goes
> through.
> I gave a command deny insert,delete,update on ssycolumns to test.
> Still I am not able to prevent user altering schema . Alter table
> successfully goes throgh.
> I do not want to use datreader and datwriter role.
> since I want user 'test' to create storred procedure with dbo owner
> Is there a way to achieve this ?
> Thanks
> M A Srinivas

You can't prevent the user from modifying/dropping an existing object. If
you need to create objects with dbo owner, then the user must be in the
db_owner role, and that means he can modify/drop any dbo object. If you can
explain why you need the test user to create stored procedures, then perhaps
someone can suggest an alternative approach. Are you creating the procedures
dynamically, are you deploying new code to several server, etc.

Simon

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||This is a security feature.
> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

ALTER TABLE In cursor

Hi, All
I have a cursor which sets the value of @.tablename to a user table and
another cursor which set the @.contraint to a valid contraint name.
Why does the statement below produce a incorrect syntax error
alter table @.tablename drop constraint @.constraint
If I substitute the variables with actual values it works fine
Please helpYou can't use parameters in an ALTER TABLE statement. Use:
EXEC ('alter table ' + @.tablename + ' drop constraint ' + @.constraint)
--
Jacco Schalkwijk
SQL Server MVP
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:89619AFE-048B-4002-AEF4-E7D0A1EA527D@.microsoft.com...
> Hi, All
> I have a cursor which sets the value of @.tablename to a user table and
> another cursor which set the @.contraint to a valid contraint name.
> Why does the statement below produce a incorrect syntax error
> alter table @.tablename drop constraint @.constraint
> If I substitute the variables with actual values it works fine
> Please help

ALTER TABLE In cursor

Hi, All
I have a cursor which sets the value of @.tablename to a user table and
another cursor which set the @.contraint to a valid contraint name.
Why does the statement below produce a incorrect syntax error
alter table @.tablename drop constraint @.constraint
If I substitute the variables with actual values it works fine
Please help
You can't use parameters in an ALTER TABLE statement. Use:
EXEC ('alter table ' + @.tablename + ' drop constraint ' + @.constraint)
Jacco Schalkwijk
SQL Server MVP
"Antony" <Antony@.discussions.microsoft.com> wrote in message
news:89619AFE-048B-4002-AEF4-E7D0A1EA527D@.microsoft.com...
> Hi, All
> I have a cursor which sets the value of @.tablename to a user table and
> another cursor which set the @.contraint to a valid contraint name.
> Why does the statement below produce a incorrect syntax error
> alter table @.tablename drop constraint @.constraint
> If I substitute the variables with actual values it works fine
> Please help

Alter Table

Hi, i think that my question is stupid, but i will ask anyway ..
I want to allow a user to create stored procedure, alter stored procedure,
and drop procedure, but the same user cant alter any table, can i do that '
?
Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200508/1You can
GRANT CREATE PROC TO username
The procedures that the user creates will that user also be able to alter an
d drop. But the user
will only be able to create procedures with that user as the owner, and the
user will only be able
to alter and drop the procedures that he/she is the owner of.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Plantador R via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:525A944123AA0@.webservertalk.com...
> Hi, i think that my question is stupid, but i will ask anyway ..
> I want to allow a user to create stored procedure, alter stored procedure,
> and drop procedure, but the same user cant alter any table, can i do that
?
> Thanks
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200508/1|||Sure you can do that.
AMB
"Plantador R via webservertalk.com" wrote:

> Hi, i think that my question is stupid, but i will ask anyway ..
> I want to allow a user to create stored procedure, alter stored procedure,
> and drop procedure, but the same user cant alter any table, can i do that
?
> Thanks
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200508/1
>|||Thanks . Ill try it ...
Message posted via http://www.webservertalk.com

Alter Table

I want to disable all the triggers of a data base (and enable all of then
after the execution of a process).
I can find out the name of the user tables looking for then in the
sysobjects table and then I can run "alter table <name> disable all" but I
have to repeat it with all the tables.
My question is: Can I write a store procedure to do this? I tried to do it
with a cursor with the name of all the tables but it doesnt't work because
the alter table expects a table name, not a variable.
Thank you.Hi,
Try out this script.
declare @.x varchar(255)
select @.x = @.x + 'alter table '+name+ 'disable trigger all'
from master.dbo.sysobjects where type='u'
exec (@.x)
go
Thanks
Hari
MCDBA
"Alberto" <alberto@.nospam.com> wrote in message
news:O9OY8kluDHA.2408@.tk2msftngp13.phx.gbl...
> I want to disable all the triggers of a data base (and enable all of then
> after the execution of a process).
> I can find out the name of the user tables looking for then in the
> sysobjects table and then I can run "alter table <name> disable all" but I
> have to repeat it with all the tables.
> My question is: Can I write a store procedure to do this? I tried to do it
> with a cursor with the name of all the tables but it doesnt't work because
> the alter table expects a table name, not a variable.
> Thank you.
>|||I thought you might need a cursor to do this. How can you tell what would
need a cursor and what would not ?
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u8VcsuluDHA.3140@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Try out this script.
>
> declare @.x varchar(255)
> select @.x = @.x + 'alter table '+name+ 'disable trigger all'
> from master.dbo.sysobjects where type='u'
> exec (@.x)
> go
> Thanks
> Hari
> MCDBA
>
> "Alberto" <alberto@.nospam.com> wrote in message
> news:O9OY8kluDHA.2408@.tk2msftngp13.phx.gbl...
> > I want to disable all the triggers of a data base (and enable all of
then
> > after the execution of a process).
> >
> > I can find out the name of the user tables looking for then in the
> > sysobjects table and then I can run "alter table <name> disable all" but
I
> > have to repeat it with all the tables.
> >
> > My question is: Can I write a store procedure to do this? I tried to do
it
> > with a cursor with the name of all the tables but it doesnt't work
because
> > the alter table expects a table name, not a variable.
> >
> > Thank you.
> >
> >
>

Alter Table

Hi, i think that my question is stupid, but i will ask anyway ..
I want to allow a user to create stored procedure, alter stored procedure,
and drop procedure, but the same user cant alter any table, can i do that '
?
Thanks
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...curity/200508/1Hi,
ALTER PROCEDURE and DROP PROCEDURE commands are not grantable. Only solution
is to give db_ddladmin tole to
this login account. In this case that user will be able to ALTER and DROP
tables.
Thanks
Hari
SQL Server MVP
"Plantador R via droptable.com" <forum@.droptable.com> wrote in message
news:525A9612E2CA8@.droptable.com...
> Hi, i think that my question is stupid, but i will ask anyway ..
> I want to allow a user to create stored procedure, alter stored procedure,
> and drop procedure, but the same user cant alter any table, can i do that
> ?
> Thanks
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...curity/200508/1

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
I saw it work in a cartoon once so I am pretty sure I can do it.
Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.
|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
--
I saw it work in a cartoon once so I am pretty sure I can do it.Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
--
I saw it work in a cartoon once so I am pretty sure I can do it.Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

alter login on Windows user groups

Hi,
I have problem with respect to "alter login" on Windows user groups (created
via Active Directory).
If I execute the statement "alter login [domain\groupname] enable" I get
an
exception saying (the exception also occurs with "alter login
[domain\groupname] disable"):
Msg 15151, Level 16, State 1, Line 1
Cannot alter the login of 'domain\groupname', because it does not exist or
you do not have permissions.
If if try to enable the login via SQL Server Management GUI I get the same
exception.
Is this because it is not at all possible to alter the logins of windows
user groups? Or is it a bug in SQL Server?
I am running MS SQL Server 2005 SP1 (compatibility level 90).
The user I am executing the statement as is "sysadmin" on the server.
Thanks in advance.
Kind Regards,
Rune Jensen
ETIAFAIK you can only disable/enable logins that represent individual windows
accounts, not windows groups.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Server App" <aaa@.yes.dk> wrote in message
news:%23VF$pTC%23GHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have problem with respect to "alter login" on Windows user groups
> (created
> via Active Directory).
> If I execute the statement "alter login [domain\groupname] enable" I g
et
> an
> exception saying (the exception also occurs with "alter login
> [domain\groupname] disable"):
> Msg 15151, Level 16, State 1, Line 1
> Cannot alter the login of 'domain\groupname', because it does not exist or
> you do not have permissions.
> If if try to enable the login via SQL Server Management GUI I get the same
> exception.
> Is this because it is not at all possible to alter the logins of windows
> user groups? Or is it a bug in SQL Server?
> I am running MS SQL Server 2005 SP1 (compatibility level 90).
> The user I am executing the statement as is "sysadmin" on the server.
> Thanks in advance.
> Kind Regards,
> Rune Jensen
> ETI
>
>

Allowing users to truncate log file

I would like to allow a particular user to truncate a log file in a
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.

Dave C.hedgracer (d.christman@.sbcglobal.net) writes:

Quote:

Originally Posted by

I would like to allow a particular user to truncate a log file in a
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.

Yes, this can be done with help of certificates. I have an article on my
web site that describes this in detail:
http://www.sommarskog.se/grantperm.html.

However, this not at all sound right to me, at least if the user would
truncate the log file every day. Truncating the log is something you
only do in exceptional cases when there is an emergency. Normally, you
either:
1) Run with full recovery and schedule regular full backups as well as
transaction log backups.
2) Run with simple recovery and schedule only full backups. The log
will be auto-truncated.

When you run with full recovery, you do so, because you want to be able
to recover the database to any given point in time. But if you truncate
the log, you lose that possibility. Which in fact is self-evident in
SQL 2005, where the only way to do this is to set the database into
simple recovery.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx