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 in Stored Procedure

Hi,
The script in the stored procedure below works. But, when creating the
stored procedure, I only see the first 'if'. There is nothing in there.
I tried through Enterprise Manager as well. Anybody knows what could be
causing this?
Thanks,
CREATE PROCEDURE sp_ImportKEYBANKAccountsFeed
AS
-- Drop Constraints
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblAccounts_tblAccountTypes]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblAccounts] DROP CONSTRAINT
[FK_tblAccounts_tblAccountTypes]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblAccTransactions_tblTransactionTyp
es]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblAccTransactions] DROP CONSTRAINT
[FK_tblAccTransactions_tblTransactionTyp
es]
GO
-- Clean tables
TRUNCATE TABLE [dbo].[tblAccounts]
GO
TRUNCATE TABLE [dbo].[tblAccountTypes]
GO
TRUNCATE TABLE [dbo].[tblAccTransactions]
GO
TRUNCATE TABLE [dbo].[tblTransactionTypes]
GO
-- Populate tables
INSERT INTO tblAccountTypes
(
AccountTypeID,
TypeDesc
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractAccountTypes
GO
INSERT INTO tblTransactionTypes
(
TransactionTypeID,
TransactionDesc
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractTranCodes
GO
INSERT INTO tblAccounts
(
KeyBankAccID,
KeyBankCustomerID,
CACNO,
AccountTypeID,
Balance,
InterestRate,
DateOpened,
MaturityDate,
MonthlyDepositAmt,
DateDeposit,
Settlement,
FinalPaymentDate,
DateMonthlyPmtDue,
DirectDebitDetails,
ArrearsAmt,
ProductDescription,
LedgerCd
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractESBAccounts
GO
INSERT INTO tblAccounts
(
KeyBankAccID,
KeyBankCustomerID,
CACNO,
AccountTypeID,
Balance,
InterestRate,
DateOpened,
MaturityDate,
MonthlyDepositAmt,
DateDeposit,
Settlement,
FinalPaymentDate,
DateMonthlyPmtDue,
DirectDebitDetails,
ArrearsAmt,
ProductDescription,
LedgerCd
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractSavingsAccounts
GO
INSERT INTO tblAccounts
(
KeyBankAccID,
KeyBankCustomerID,
CACNO,
AccountTypeID,
Balance,
InterestRate,
DateOpened,
MaturityDate,
MonthlyDepositAmt,
DateDeposit,
Settlement,
FinalPaymentDate,
DateMonthlyPmtDue,
DirectDebitDetails,
ArrearsAmt,
ProductDescription,
LedgerCd
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractPLAccounts
GO
INSERT INTO tblAccTransactions
(
KeyBankTransID,
KeyBankCustomerID,
AccountID,
TransactionTypeID,
Reference,
Debit,
Credit,
Balance,
Arrears,
BookingDate,
Amount,
Narrative
)
EXEC [BOSID042].[BOSI_DEV].[dbo].retExtractTrans
GO
--Add Constraints back to tables
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblAccounts_tblAccountTypes]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblAccounts] ADD CONSTRAINT
[FK_tblAccounts_tblAccountTypes]
FOREIGN KEY ([AccountTypeID]) REFERENCES [tblAccountTypes]
([AccountTypeID])
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo]. [FK_tblAccTransactions_tblTransactionTyp
es]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblAccTransactions] ADD CONSTRAINT
[FK_tblAccTransactions_tblTransactionTyp
es]
FOREIGN KEY([TransactionTypeID] ) REFERENCES [tblTransactionTypes]
([TransactionTypeID])
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*** Sent via Developersdex http://www.examnotes.net ***That's because GO is a batch terminator
If you do sp_helptext 'ImportKEYBANKAccountsFeed ' you will see that
the procedure stops after the first GO
Take out the GO's
Denis the SQL Menace
http://sqlservercode.blogspot.com/

ALTER table in a SP

Hi. I have a stored procedure where I'm creating a Temporary table,
inserting data into it from another stored procedure, than altering it by
adding another column, then doing more stuff within the temp table.
My problem is after my Alter statment, I need to use a GO statement to the
table will be altered...but then the stored procedure thinks it's done and
doesn't run anything else.
Either: How do I insert the new column in after inserting into the table?
OR create the table with the extra column and tell the SP to run...and not
bomb when it thinks it's missing a column?
Thanks.
-Rob T.
PS...it work fine just in the Query Designer, but the GO is killing me when
I'm created the SP...
Here's a snippit of the SP:
CREATE PROCEDURE GLGroupCalc(@.GrpID int) AS
-- Create a #Temp table --
create table #Temp (GrpDtlID int, seq int, DtlDesc VarChar(40), ParentID
int, Level1 int, ParentSeq int)
insert into #Temp exec GLGroup @.GrpID
ALTER TABLE #Temp ADD Total numeric(18, 2) NOT NULL DEFAULT 0
GO
--Figure out all the values --
... a bunch of other stuff in here...
select * from #Temp
drop table #Temp
GOWhy not just create the table with the ultimate # of columns from the
get-go'
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:eQdK6zaUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Hi. I have a stored procedure where I'm creating a Temporary table,
> inserting data into it from another stored procedure, than altering it by
> adding another column, then doing more stuff within the temp table.
> My problem is after my Alter statment, I need to use a GO statement to the
> table will be altered...but then the stored procedure thinks it's done
> and doesn't run anything else.
> Either: How do I insert the new column in after inserting into the table?
> OR create the table with the extra column and tell the SP to run...and
> not bomb when it thinks it's missing a column?
> Thanks.
> -Rob T.
> PS...it work fine just in the Query Designer, but the GO is killing me
> when I'm created the SP...
> Here's a snippit of the SP:
> CREATE PROCEDURE GLGroupCalc(@.GrpID int) AS
> -- Create a #Temp table --
> create table #Temp (GrpDtlID int, seq int, DtlDesc VarChar(40), ParentID
> int, Level1 int, ParentSeq int)
> insert into #Temp exec GLGroup @.GrpID
> ALTER TABLE #Temp ADD Total numeric(18, 2) NOT NULL DEFAULT 0
> GO
> --Figure out all the values --
> ... a bunch of other stuff in here...
> select * from #Temp
> drop table #Temp
> GO
>|||You can't put GO in an SP.
The solution is to do it this way:
CREATE TABLE #Temp (grpdtlid INT, seq INT, dtldesc VARCHAR(40), parentid
INT, level1 INT, parentseq INT, total NUMERIC(18,2) NOT NULL DEFAULT 0)
INSERT INTO #Temp
(grpdtlid, seq, dtldesc, parentid, level1, parentseq)
EXEC GLGroup
... etc
David Portas
SQL Server MVP
--|||I tried that...but when I run "insert into #Temp exec GLGroup @.GrpID" it
bombs since the number of columns I'm getting from GLGroup doesn't match the
number of columns I'm inserting to...
I thought I was being clever by creating a table that matched the structure
of the SP and then altering it. ;-)
Is there a way to insert into my table where the number of columns don't
match?
"Michael C#" <howsa@.boutdat.com> wrote in message
news:u0B6E4aUFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Why not just create the table with the ultimate # of columns from the
> get-go'
> "Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
> news:eQdK6zaUFHA.4056@.TK2MSFTNGP15.phx.gbl...
>|||Specify column names in the INSERT.
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:%23YgU27aUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>I tried that...but when I run "insert into #Temp exec GLGroup @.GrpID" it
>bombs since the number of columns I'm getting from GLGroup doesn't match
>the number of columns I'm inserting to...
> I thought I was being clever by creating a table that matched the
> structure of the SP and then altering it. ;-)
> Is there a way to insert into my table where the number of columns don't
> match?
>
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:u0B6E4aUFHA.3620@.TK2MSFTNGP09.phx.gbl...
>|||Well, you could do something like
SELECT *, CONVERT(datatype_of_new_col, 'newColVal') INTO
#secondTempTable
FROM #firstTempTable
Or try to avoid the need for the additional columns in the first place
Or create a second stored procedure that includes the additional columns you
want
Or see http://www.sommarskog.se/share_data.html
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:eQdK6zaUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Hi. I have a stored procedure where I'm creating a Temporary table,
> inserting data into it from another stored procedure, than altering it by
> adding another column, then doing more stuff within the temp table.
> My problem is after my Alter statment, I need to use a GO statement to the
> table will be altered...but then the stored procedure thinks it's done
> and doesn't run anything else.
> Either: How do I insert the new column in after inserting into the table?
> OR create the table with the extra column and tell the SP to run...and
> not bomb when it thinks it's missing a column?
> Thanks.
> -Rob T.
> PS...it work fine just in the Query Designer, but the GO is killing me
> when I'm created the SP...
> Here's a snippit of the SP:
> CREATE PROCEDURE GLGroupCalc(@.GrpID int) AS
> -- Create a #Temp table --
> create table #Temp (GrpDtlID int, seq int, DtlDesc VarChar(40), ParentID
> int, Level1 int, ParentSeq int)
> insert into #Temp exec GLGroup @.GrpID
> ALTER TABLE #Temp ADD Total numeric(18, 2) NOT NULL DEFAULT 0
> GO
> --Figure out all the values --
> ... a bunch of other stuff in here...
> select * from #Temp
> drop table #Temp
> GO
>|||Yes, see David's reply. Just make sure the columns you leave out are either
NULLable or NOT NULL with a DEFAULT.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:%23YgU27aUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>I tried that...but when I run "insert into #Temp exec GLGroup @.GrpID" it
>bombs since the number of columns I'm getting from GLGroup doesn't match
>the number of columns I'm inserting to...
> I thought I was being clever by creating a table that matched the
> structure of the SP and then altering it. ;-)
> Is there a way to insert into my table where the number of columns don't
> match?
>
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:u0B6E4aUFHA.3620@.TK2MSFTNGP09.phx.gbl...
>|||What is the error you get? You can alter a temporary table by adding a
column or constraint in a stored procedure, and I don't see anything wrong
with your code so far.
Jacco Schalkwijk
SQL Server MVP
"Rob T" <RTorcellini@.DONTwalchemSPAM.com> wrote in message
news:eQdK6zaUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Hi. I have a stored procedure where I'm creating a Temporary table,
> inserting data into it from another stored procedure, than altering it by
> adding another column, then doing more stuff within the temp table.
> My problem is after my Alter statment, I need to use a GO statement to the
> table will be altered...but then the stored procedure thinks it's done
> and doesn't run anything else.
> Either: How do I insert the new column in after inserting into the table?
> OR create the table with the extra column and tell the SP to run...and
> not bomb when it thinks it's missing a column?
> Thanks.
> -Rob T.
> PS...it work fine just in the Query Designer, but the GO is killing me
> when I'm created the SP...
> Here's a snippit of the SP:
> CREATE PROCEDURE GLGroupCalc(@.GrpID int) AS
> -- Create a #Temp table --
> create table #Temp (GrpDtlID int, seq int, DtlDesc VarChar(40), ParentID
> int, Level1 int, ParentSeq int)
> insert into #Temp exec GLGroup @.GrpID
> ALTER TABLE #Temp ADD Total numeric(18, 2) NOT NULL DEFAULT 0
> GO
> --Figure out all the values --
> ... a bunch of other stuff in here...
> select * from #Temp
> drop table #Temp
> GO
>|||> What is the error you get? You can alter a temporary table by adding a
> column or constraint in a stored procedure,
But you can't reference it directly, as the parser doesn't read ahead.
CREATE PROCEDURE dbo.foo
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #foo (id INT)
ALTER TABLE #foo ADD bar VARCHAR(32)
-- this works fine
SELECT * FROM #foo
-- this fails:
SELECT id, bar FROM #foo
-- so does this:
UPDATE #foo SET bar = 5
DROP TABLE #foo
END
GO
EXEC dbo.foo
GO
DROP PROCEDURE dbo.foo
GO
Server: Msg 207, Level 16, State 3, Procedure foo, Line 14
Invalid column name 'bar'.
Server: Msg 207, Level 16, State 1, Procedure foo, Line 17
Invalid column name 'bar'.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Defered resolution at its best...Though, this is an easy fix. ;-)
CREATE PROCEDURE dbo.foo
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #foo (id INT)
exec('ALTER TABLE #foo ADD bar VARCHAR(32)')
-- this works fine
SELECT * FROM #foo
-- this fails:
SELECT id, bar FROM #foo
-- so does this:
UPDATE #foo SET bar = 5
DROP TABLE #foo
END
GO
EXEC dbo.foo
GO
DROP PROCEDURE dbo.foo
GO
-oj
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:O2D2gCbUFHA.1432@.TK2MSFTNGP09.phx.gbl...
> But you can't reference it directly, as the parser doesn't read ahead.
> CREATE PROCEDURE dbo.foo
> AS
> BEGIN
> SET NOCOUNT ON
> CREATE TABLE #foo (id INT)
> ALTER TABLE #foo ADD bar VARCHAR(32)
> -- this works fine
> SELECT * FROM #foo
> -- this fails:
> SELECT id, bar FROM #foo
> -- so does this:
> UPDATE #foo SET bar = 5
> DROP TABLE #foo
> END
> GO
> EXEC dbo.foo
> GO
> DROP PROCEDURE dbo.foo
> GO
>
> Server: Msg 207, Level 16, State 3, Procedure foo, Line 14
> Invalid column name 'bar'.
> Server: Msg 207, Level 16, State 1, Procedure foo, Line 17
> Invalid column name 'bar'.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>

Alphanumeric Autonumber Primary Key

Hi there,
The age old question of creating a unique alphanumeric value automatically like ABC0001, ABC0002

Is it possible to do this automatically? That is, without having to update it which will slow the db down horribly?the only sane way of doing it is to have an ordinary integer identity column, then produce the alphanumeric value in a view

create view myview as
select 'ABC'+right(cast(pkey as varchar(9)),4) as myalnumkey ...

Almost Replicated - I think

I have a new server, new instance of SQL on the network
with an old server,old SQL db. I attempted to set up
replication by creating a snapshot and making new server
(Win 2k3) a subscriber to publisher/distributor. I get
the following error:
Invalid column name ', '.
(Source: NewServer (Data source); Error number: 207)
Confused (1) because NewServer had nothing on it (only
standard SQL install db and (2) don't know where to go
next.
Any help, ideas?
TIA
Rob
Rob,
if you have been replicating a view then I have seen this before. This
problem occurs because the Snapshot Agent always sets the QUOTED_IDENTIFIER
option to ON, regardless of the actual setting. Therefore, if the stored
procedures or views use double quotation marks, the Distribution Agent or
the Merge Agent assumes the default behavior of using double quotation marks
for identifiers only. To get round this, you can change the object script to
refer to literals using single quotes, or use DTS to transfer the objects.
If this is not the issue, I came across this error in merge replication that
might be of use:
http://support.microsoft.com/default...b;en-us;821535
HTH,
Paul Ibison

Allowing Transformations when Creating Publication for Replication

I am at my wits end here. For Replication the Books Online clearly state:

"The option to allow transformations is set at the time you create a publication"

However, I cannot find any options that allow me to do this in the Create Publication Wizard.

Once the Publication has been created I see in the Properties in the Subscription Options tab that "Use DTS to transform data before distributing it to a Subscriber" is set to No and there is no way to change it.

Where am I going wrong?I'd actually like to know the EXACT same thing. I'm trying to use replciation and need only to do some transformations to the data, but as you mention that option is greyed out.

Allowing multi-element user defined custom data

We are creating a phonebook application which allows users to add custom
data to each entry, in the form of a (name):(value) pair. The users should
be able to add names of custom data types to a look-up table, and then be
able to add data values of that "type" to any entry in the phonebook. The
data will be saved as nvarchar.
However, we now realize some of this custom data will have to be made up
of several data elements in itself. For example: If the users want to add
the data type "address at in-law's", this custom data will be more than just
(name):(long string value), it will have to be (name):((street)(city)(state)(zipcode)).
We have several ideas on how to do this but they all seem cumbersome, and
they complicate the design a lot. Has anyone created a system like this before?
Is there a tried and true way of doing this?Hi
Have you thought of using XML for this?
John
"Ido Kalir" wrote:
> We are creating a phonebook application which allows users to add custom
> data to each entry, in the form of a (name):(value) pair. The users should
> be able to add names of custom data types to a look-up table, and then be
> able to add data values of that "type" to any entry in the phonebook. The
> data will be saved as nvarchar.
> However, we now realize some of this custom data will have to be made up
> of several data elements in itself. For example: If the users want to add
> the data type "address at in-law's", this custom data will be more than just
> (name):(long string value), it will have to be (name):((street)(city)(state)(zipcode)).
> We have several ideas on how to do this but they all seem cumbersome, and
> they complicate the design a lot. Has anyone created a system like this before?
> Is there a tried and true way of doing this?
>

All Users are being IDed as 'dbo'

We have a SQL Server 2005 database set up. We are trying to add new users to one of four define roles. Even though we are creating new login, then assigning each new login to one of the four roles. The server is returning 'dbo' as the user no matter who is logging in. Is there some setting that is causing this behavior?

Thanks of any help.

Can you post more information about the roles you mentioned and the commands you used to create the logins and assign them role memberships?

Thanks
Laurentiu

|||We have created 4 roles with permission to a select set of stored procedures. When one of the front end applications opens, it runs a procedure that gets the USER id and which of one or more roles that user has. On the test system, each of the users are properly ID and shown the correct roles. But on the Production system, all of the login/users return the 'dbo' USER ID, thus the roles are not indicated correctly. We believe that using "SQL Server Management Studio 2005", is setting all the logins to 'dbo' even though when we look at the settings, it shows the proper roles for each login.|||

One likely possibility is that in your production system, the client is using credentials with SYSADMIN privileges (i.e. the login they are using is a member of the server fixed role SYSADMIN). Members of SYSADMIN will always have a user-identity of “dbo” in any database in the system.

-Raul Garcia

SDE/T

SQL Server Engine

All of a sudden i Cant Create Stored Procedures?

When creating even the simplest of Stored Procedures (that i know work!), i get prompted with an error Titled : <Microsoft SQL-DMO (ODBC SQLSTATE: 42000)> with an error message: <Error 170: Line 1: Incorrect syntax near "My Stored Proc Name". Must declare the variable '@.str_PartNo'>

I know for a fact that the stored procedure does not have a syntax error because i can run a stored proc that is already saved, but if i copy and paste it into a new store proc, i get this error! Also, when i click the check syntax there are no errors.

I was told to verify if the service <SQL Server Agent> is running. It is.

I could really use some helpThe agent won't affect this
Suspect either you aren't copying the correct data or there is an invalid character somewhere.

First thing to do is look for the definition of @.str_PartNo and see why it is not defined.
The message is saying it has a problem at line 1 which is a bit odd - do you have an exec of dynamic sql after a go somewhere?

try
create proc myproc
as
select 1
go

The copy and paste may be introducing errors due to invalid characters.|||it works, it seems the problem was that i was encompassing my Stored Proc name with double quotes, i've saved others with the double quotes...strange.

Thanks for the help|||It's because of the settings in the environment you are creating the sp from.
look at

set QUOTED_IDENTIFIER off
go
create procedure "mysp"
as
select 1
go

set QUOTED_IDENTIFIER on
go
create procedure "mysp"
as
select 1
go

drop procedure mysp