DDL Permissions - CREATE PROCEDURE, but no CREATE TABLE

Env: SQL Server 2000 SP3a on Windows 2k Server SP4
I want to allow devlopers to create and alter sprocs in the dbo
schema, but not create or alter tables (in any schema). I tried:
GRANT CREATE PROCEDURE TO <user>
...but it will only allow the user to create procedures within their
own user schema - not in the dbo schema. "Server: Msg 2760, Level 16,
State 1, Procedure testspo3, Line 2 Specified owner name 'dbo' either
does not exist or you do not have permission to use it."
So then I tried adding the user to the ddl_admin fixed db role and
then executing:
DENY CREATE TABLE TO <user>
...I thought I was OK at first. The user could create dbo owned
sprocs, alter them, and not create tables in any schema. BUT, they
can DROP TABLE! Of course, you can't DENY DROP <object>.
Any idears?
TIA,
-PeterHave you tried to use only the REFERENCES permission on the table for the
user creationg the SP? Check the "Owners and Permissions" topic in Books
OnLine
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ar
chitec.chm::/8_ar_da_2s4z.htm).
HTH,
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0401021018.12de3b15@.posting.google.com...

quote:

---

> Env: SQL Server 2000 SP3a on Windows 2k Server SP4
> I want to allow devlopers to create and alter sprocs in the dbo
> schema, but not create or alter tables (in any schema). I tried:
> GRANT CREATE PROCEDURE TO <user>
> ...but it will only allow the user to create procedures within their
> own user schema - not in the dbo schema. "Server: Msg 2760, Level 16,
> State 1, Procedure testspo3, Line 2 Specified owner name 'dbo' either
> does not exist or you do not have permission to use it."
> So then I tried adding the user to the ddl_admin fixed db role and
> then executing:
> DENY CREATE TABLE TO <user>
> ...I thought I was OK at first. The user could create dbo owned
> sprocs, alter them, and not create tables in any schema. BUT, they
> can DROP TABLE! Of course, you can't DENY DROP <object>.
> Any idears?
> TIA,
> -Peter

---

|||Thank you for the reposnse, but I think you missed the question. Your
reponse is angled towards data access permissions. My question is
about object creation permissions. I want a devloper to be able to
CREATE and ALTER stored procedures in the dbo schema, but not be able
to CREATE, ALTER, or DROP tables or other objects.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<ua9a#ud0D
HA.2324@.TK2MSFTNGP09.phx.gbl>...[QUOTE]
> Have you tried to use only the REFERENCES permission on the table for the
> user creationg the SP? Check the "Owners and Permissions" topic in Books
> OnLine
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\
ar
> chitec.chm::/8_ar_da_2s4z.htm).
> HTH,
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Peter Daniels" <nospampedro@.yahoo.com> wrote in message
> news:2fd8f155.0401021018.12de3b15@.posting.google.com...|||Sorry, you are correct, I misread the original message. Unfortunately I
don't think it is possible to acheive what you want to achieve. I guess you
should take care who can create the procedures, so you can trust the person,
if you want this person to use the dbo user.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0401051432.6deec0fb@.posting.google.com...

quote:

---

> Thank you for the reposnse, but I think you missed the question. Your
> reponse is angled towards data access permissions. My question is
> about object creation permissions. I want a devloper to be able to
> CREATE and ALTER stored procedures in the dbo schema, but not be able
> to CREATE, ALTER, or DROP tables or other objects.
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in

---

message news:<ua9a#ud0DHA.2324@.TK2MSFTNGP09.phx.gbl>...[QUOTE]
the[QUOTE]
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ar[QUO
TE]|||I cam to the same conclusion thru my research. Will SQL Server Yukon
provide better permissions granularity to provide what I'm looking
for?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<#pcwG6C1D
HA.2224@.TK2MSFTNGP10.phx.gbl>...[QUOTE]
> Sorry, you are correct, I misread the original message. Unfortunately I
> don't think it is possible to acheive what you want to achieve. I guess yo
u
> should take care who can create the procedures, so you can trust the perso
n,
> if you want this person to use the dbo user.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "Peter Daniels" <nospampedro@.yahoo.com> wrote in message
> news:2fd8f155.0401051432.6deec0fb@.posting.google.com...
> message news:<ua9a#ud0DHA.2324@.TK2MSFTNGP09.phx.gbl>...
> the
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books
\ar|||Does anyone have any other solutions to this? It seems like it should
be very easy to allow developers to create and alter sprocs under the
dbo schema, but not do any other DDL (such as DROP TABLE or DROP
PROCEDURE).
nospampedro@.yahoo.com (Peter Daniels) wrote in message news:<2fd8f155.0401081203.4e75bc46@.posting.
google.com>...[QUOTE]
> I cam to the same conclusion thru my research. Will SQL Server Yukon
> provide better permissions granularity to provide what I'm looking
> for?
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:<#pcwG6C1DHA.2224@.TK2MSFTNGP10.phx.gbl>...
> message news:<ua9a#ud0DHA.2324@.TK2MSFTNGP09.phx.gbl>...
> the
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books
\ar

DDL Permissions

Ok, folks, I got one for you. I want to allow a user to make schema changes to
tables in a database that are owned by dbo. However, I do not want this user to
do anything beyond that, such as add roles or change permissions on objects,
etc. It appears that the db_ddladmin fixed database role only allows the user to
create, delete, and alter objects owned by themselves. And, the only way to get
what I want is to add the user to the db_owner fixed database role. Not really
what I had in mind. Am I missing something here? Can anybody give me any
direction on this?
Thanks in advance. You guys rock!
Darrell
db_ddladmin can alter and drop object owned by others.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eLeP7MhNFHA.568@.TK2MSFTNGP09.phx.gbl...
> Ok, folks, I got one for you. I want to allow a user to make schema changes to tables in a
> database that are owned by dbo. However, I do not want this user to do anything beyond that, such
> as add roles or change permissions on objects, etc. It appears that the db_ddladmin fixed database
> role only allows the user to create, delete, and alter objects owned by themselves. And, the only
> way to get what I want is to add the user to the db_owner fixed database role. Not really what I
> had in mind. Am I missing something here? Can anybody give me any direction on this?
> Thanks in advance. You guys rock!
> Darrell
|||db_ddladmin is able to modify all tables in the database. Use Query Analyzer
to alter tables instead of Enterprise Manager if you don't want to see those
warning messages.
"Darrell" wrote:

> Ok, folks, I got one for you. I want to allow a user to make schema changes to
> tables in a database that are owned by dbo. However, I do not want this user to
> do anything beyond that, such as add roles or change permissions on objects,
> etc. It appears that the db_ddladmin fixed database role only allows the user to
> create, delete, and alter objects owned by themselves. And, the only way to get
> what I want is to add the user to the db_owner fixed database role. Not really
> what I had in mind. Am I missing something here? Can anybody give me any
> direction on this?
> Thanks in advance. You guys rock!
> Darrell
>
|||Jack wrote:
> db_ddladmin is able to modify all tables in the database. Use Query Analyzer
> to alter tables instead of Enterprise Manager if you don't want to see those
> warning messages.
> "Darrell" wrote:
>
Thanks to both of you for the responses. I like the Query Analyzer suggestion,
however, I have a bunch of GUI-loving developers that probably couldn't spell
T-SQL. But I digress...
A follow-up question, then, is can they make changes to the tables in the
database diagrammer and those changes will be saved back to the tables?
Thanks again.

DDL Permissions

Ok, folks, I got one for you. I want to allow a user to make schema changes
to
tables in a database that are owned by dbo. However, I do not want this user
to
do anything beyond that, such as add roles or change permissions on objects,
etc. It appears that the db_ddladmin fixed database role only allows the use
r to
create, delete, and alter objects owned by themselves. And, the only way to
get
what I want is to add the user to the db_owner fixed database role. Not real
ly
what I had in mind. Am I missing something here? Can anybody give me any
direction on this?
Thanks in advance. You guys rock!
Darrelldb_ddladmin can alter and drop object owned by others.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eLeP7MhNFHA.568@.TK2MSFTNGP09.phx.gbl...
> Ok, folks, I got one for you. I want to allow a user to make schema change
s to tables in a
> database that are owned by dbo. However, I do not want this user to do any
thing beyond that, such
> as add roles or change permissions on objects, etc. It appears that the db
_ddladmin fixed database
> role only allows the user to create, delete, and alter objects owned by th
emselves. And, the only
> way to get what I want is to add the user to the db_owner fixed database r
ole. Not really what I
> had in mind. Am I missing something here? Can anybody give me any directio
n on this?
> Thanks in advance. You guys rock!
> Darrell|||db_ddladmin is able to modify all tables in the database. Use Query Analyze
r
to alter tables instead of Enterprise Manager if you don't want to see those
warning messages.
"Darrell" wrote:

> Ok, folks, I got one for you. I want to allow a user to make schema change
s to
> tables in a database that are owned by dbo. However, I do not want this us
er to
> do anything beyond that, such as add roles or change permissions on object
s,
> etc. It appears that the db_ddladmin fixed database role only allows the u
ser to
> create, delete, and alter objects owned by themselves. And, the only way t
o get
> what I want is to add the user to the db_owner fixed database role. Not re
ally
> what I had in mind. Am I missing something here? Can anybody give me any
> direction on this?
> Thanks in advance. You guys rock!
> Darrell
>|||Jack wrote:
> db_ddladmin is able to modify all tables in the database. Use Query Analy
zer
> to alter tables instead of Enterprise Manager if you don't want to see tho
se
> warning messages.
> "Darrell" wrote:
>
Thanks to both of you for the responses. I like the Query Analyzer suggestio
n,
however, I have a bunch of GUI-loving developers that probably couldn't spel
l
T-SQL. But I digress...
A follow-up question, then, is can they make changes to the tables in the
database diagrammer and those changes will be saved back to the tables?
Thanks again.

DDL Permissions

Ok, folks, I got one for you. I want to allow a user to make schema changes to
tables in a database that are owned by dbo. However, I do not want this user to
do anything beyond that, such as add roles or change permissions on objects,
etc. It appears that the db_ddladmin fixed database role only allows the user to
create, delete, and alter objects owned by themselves. And, the only way to get
what I want is to add the user to the db_owner fixed database role. Not really
what I had in mind. Am I missing something here? Can anybody give me any
direction on this?
Thanks in advance. You guys rock!
Darrelldb_ddladmin can alter and drop object owned by others.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:eLeP7MhNFHA.568@.TK2MSFTNGP09.phx.gbl...
> Ok, folks, I got one for you. I want to allow a user to make schema changes to tables in a
> database that are owned by dbo. However, I do not want this user to do anything beyond that, such
> as add roles or change permissions on objects, etc. It appears that the db_ddladmin fixed database
> role only allows the user to create, delete, and alter objects owned by themselves. And, the only
> way to get what I want is to add the user to the db_owner fixed database role. Not really what I
> had in mind. Am I missing something here? Can anybody give me any direction on this?
> Thanks in advance. You guys rock!
> Darrell|||db_ddladmin is able to modify all tables in the database. Use Query Analyzer
to alter tables instead of Enterprise Manager if you don't want to see those
warning messages.
"Darrell" wrote:
> Ok, folks, I got one for you. I want to allow a user to make schema changes to
> tables in a database that are owned by dbo. However, I do not want this user to
> do anything beyond that, such as add roles or change permissions on objects,
> etc. It appears that the db_ddladmin fixed database role only allows the user to
> create, delete, and alter objects owned by themselves. And, the only way to get
> what I want is to add the user to the db_owner fixed database role. Not really
> what I had in mind. Am I missing something here? Can anybody give me any
> direction on this?
> Thanks in advance. You guys rock!
> Darrell
>|||Jack wrote:
> db_ddladmin is able to modify all tables in the database. Use Query Analyzer
> to alter tables instead of Enterprise Manager if you don't want to see those
> warning messages.
> "Darrell" wrote:
>
Thanks to both of you for the responses. I like the Query Analyzer suggestion,
however, I have a bunch of GUI-loving developers that probably couldn't spell
T-SQL. But I digress...
A follow-up question, then, is can they make changes to the tables in the
database diagrammer and those changes will be saved back to the tables?
Thanks again.

dbo user / permissions error.

I had found one or two other questions about this, but neither of them seemed to be the same as my instance, and there solutions were not valid for me.

I purchased the Build a Program Now MS Visual C# 2005 Express Edition book with CD.

This was installed by the auto installer on the disk. which included the SQL Express 2005 version.

I have used the Visual Express C# interface, build a small program, created the database, created tables. all went well. I can even add data, and so forth.

My problem came when I went to click on database Diagrams in the Database Explorer.

I get a Dialog box stating

" This Database does not have a valid dbo user or you do not have permissions to impersonate the dbo user, so database diagramming is not available. Do you want to make yourself the dbo of this database in order to use the database diagramming?"

two options are Yes and No.

If I choose yes, I get the following dialog box stating,

" This databse does not have a valid dbo user or you do not have permissions to impresonate the dbo user, so database diagramming is not available. Ensure the dbo account is valid and ensure you have imprersonate permission on the dbo account."

One button, OK.

I have searched every where I can think of to try to figure out how to set / alter the dbo user info / permissions.

Can someone guide me on this?

Thanks in advance.

Tom

hi Tom,

try explicitely change the ownerwhip of the database to a valid principal..

start SQL Server Management Studio Express (http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en), select the database you are interested in, access it's properties and set the ownership to "sa" or another valid Login..

or, execute the statements

USE yorDatabase; GO EXEC sp_changedbowner @.loginame = 'sa', @.map= 'true';

http://msdn2.microsoft.com/en-us/library/ms178630.aspx

regards

|||

Andrea,

Thank you very much for the time and for sharing your knowledge.

I am trying to do as you stated above, however, when i start SMSE, I do not see the Database that i am actually using.

It shows the sql server itself, but does not show anything other than system items in it.

The program i am building is using a CDS.mdf, and I can not locate that anywhere in SMSE.

i am still going over it all, and doing some reading from the first link above that you sent.

If you have any other ideals, please send them my way.

Regards

Tom

|||

Think maybe I have done something wrong when i installed SQL Express or v c# Express?

I have went over every thing i can find over and over,, to no resolve.

Flustered

Tom

|||

Anyone else have any ideals?

Totally lost and stuck

Tom

|||

Hi Tom,

Check out my answer at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=690061&SiteID=1, it will explain why you can't see your database.

Mike

|||

Good Day Mike,

Just wanted to say thanks for the input and info. I am still working to resolve this issue, just been slow as I had some health issues.

Thanks again, and I'll let ya know later today if I can get a resolution using this info.

Regards

Tom

|||

Hey Mike.

I just wanted to let you know that I tried doing what was instructed in the other post.

It has not resolved my issue, I still cant use the manager to locate/attatch the dbase to work with its properties.

However its not a major issue, I am using a back door in Visual to make my relations and FK settings, even if I cant go through the one step because of the permissions error, there working all the same.

Regards

Tom

|||Here is my solution to this problem. I had this same issue. After searching this forum I had the cues I needed to correct the issue but none of the suggested methods worked. I think the problem stemed from the fact that I had renamed the primary windows user account to my name (the original Owner account left from the OS install). In the folder structure some of the folders were still titled "Owner" but further down the tree the new name was used. So this account was previously "Owner" but had been changed to my name. The solution was the create a new Windows account, all of the folders had the new name associated with them. Then I recreated the database and the problem accessing the database diagram didn't reoccur. I hope this helps. Fyi: I am using Visual Basic 2005 Express but I think it is the same issue.|||

There are two options to resolve the dbo owner issue, one is to set a new owner as described in one of the above posts (and in some situations doe not work) and the other is to change the compatability level of your database to "SQL Server 2005 (90)".

In order to do so you should right-click the database in the Management Studio; and then select:

Properties --> Options --> Change "Compatibility level" to "SQL Server 2005 (90)"

Good luck

- Gal Cohen

dbo user / permissions error.

I had found one or two other questions about this, but neither of them seemed to be the same as my instance, and there solutions were not valid for me.

I purchased the Build a Program Now MS Visual C# 2005 Express Edition book with CD.

This was installed by the auto installer on the disk. which included the SQL Express 2005 version.

I have used the Visual Express C# interface, build a small program, created the database, created tables. all went well. I can even add data, and so forth.

My problem came when I went to click on database Diagrams in the Database Explorer.

I get a Dialog box stating

" This Database does not have a valid dbo user or you do not have permissions to impersonate the dbo user, so database diagramming is not available. Do you want to make yourself the dbo of this database in order to use the database diagramming?"

two options are Yes and No.

If I choose yes, I get the following dialog box stating,

" This databse does not have a valid dbo user or you do not have permissions to impresonate the dbo user, so database diagramming is not available. Ensure the dbo account is valid and ensure you have imprersonate permission on the dbo account."

One button, OK.

I have searched every where I can think of to try to figure out how to set / alter the dbo user info / permissions.

Can someone guide me on this?

Thanks in advance.

Tom

hi Tom,

try explicitely change the ownerwhip of the database to a valid principal..

start SQL Server Management Studio Express (http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en), select the database you are interested in, access it's properties and set the ownership to "sa" or another valid Login..

or, execute the statements

USE yorDatabase;

GO

EXEC sp_changedbowner @.loginame = 'sa', @.map= 'true';

http://msdn2.microsoft.com/en-us/library/ms178630.aspx

regards

|||

Andrea,

Thank you very much for the time and for sharing your knowledge.

I am trying to do as you stated above, however, when i start SMSE, I do not see the Database that i am actually using.

It shows the sql server itself, but does not show anything other than system items in it.

The program i am building is using a CDS.mdf, and I can not locate that anywhere in SMSE.

i am still going over it all, and doing some reading from the first link above that you sent.

If you have any other ideals, please send them my way.

Regards

Tom

|||

Think maybe I have done something wrong when i installed SQL Express or v c# Express?

I have went over every thing i can find over and over,, to no resolve.

Flustered

Tom

|||

Anyone else have any ideals?

Totally lost and stuck

Tom

|||

Hi Tom,

Check out my answer at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=690061&SiteID=1, it will explain why you can't see your database.

Mike

|||

Good Day Mike,

Just wanted to say thanks for the input and info. I am still working to resolve this issue, just been slow as I had some health issues.

Thanks again, and I'll let ya know later today if I can get a resolution using this info.

Regards

Tom

|||

Hey Mike.

I just wanted to let you know that I tried doing what was instructed in the other post.

It has not resolved my issue, I still cant use the manager to locate/attatch the dbase to work with its properties.

However its not a major issue, I am using a back door in Visual to make my relations and FK settings, even if I cant go through the one step because of the permissions error, there working all the same.

Regards

Tom

|||Here is my solution to this problem. I had this same issue. After searching this forum I had the cues I needed to correct the issue but none of the suggested methods worked. I think the problem stemed from the fact that I had renamed the primary windows user account to my name (the original Owner account left from the OS install). In the folder structure some of the folders were still titled "Owner" but further down the tree the new name was used. So this account was previously "Owner" but had been changed to my name. The solution was the create a new Windows account, all of the folders had the new name associated with them. Then I recreated the database and the problem accessing the database diagram didn't reoccur. I hope this helps. Fyi: I am using Visual Basic 2005 Express but I think it is the same issue.|||

There are two options to resolve the dbo owner issue, one is to set a new owner as described in one of the above posts (and in some situations doe not work) and the other is to change the compatability level of your database to "SQL Server 2005 (90)".

In order to do so you should right-click the database in the Management Studio; and then select:

Properties --> Options --> Change "Compatibility level" to "SQL Server 2005 (90)"

Good luck

- Gal Cohen

dbo user / permissions error.

I had found one or two other questions about this, but neither of them seemed to be the same as my instance, and there solutions were not valid for me.

I purchased the Build a Program Now MS Visual C# 2005 Express Edition book with CD.

This was installed by the auto installer on the disk. which included the SQL Express 2005 version.

I have used the Visual Express C# interface, build a small program, created the database, created tables. all went well. I can even add data, and so forth.

My problem came when I went to click on database Diagrams in the Database Explorer.

I get a Dialog box stating

" This Database does not have a valid dbo user or you do not have permissions to impersonate the dbo user, so database diagramming is not available. Do you want to make yourself the dbo of this database in order to use the database diagramming?"

two options are Yes and No.

If I choose yes, I get the following dialog box stating,

" This databse does not have a valid dbo user or you do not have permissions to impresonate the dbo user, so database diagramming is not available. Ensure the dbo account is valid and ensure you have imprersonate permission on the dbo account."

One button, OK.

I have searched every where I can think of to try to figure out how to set / alter the dbo user info / permissions.

Can someone guide me on this?

Thanks in advance.

Tom

hi Tom,

try explicitely change the ownerwhip of the database to a valid principal..

start SQL Server Management Studio Express (http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en), select the database you are interested in, access it's properties and set the ownership to "sa" or another valid Login..

or, execute the statements

USE yorDatabase;

GO

EXEC sp_changedbowner @.loginame = 'sa', @.map= 'true';

http://msdn2.microsoft.com/en-us/library/ms178630.aspx

regards

|||

Andrea,

Thank you very much for the time and for sharing your knowledge.

I am trying to do as you stated above, however, when i start SMSE, I do not see the Database that i am actually using.

It shows the sql server itself, but does not show anything other than system items in it.

The program i am building is using a CDS.mdf, and I can not locate that anywhere in SMSE.

i am still going over it all, and doing some reading from the first link above that you sent.

If you have any other ideals, please send them my way.

Regards

Tom

|||

Think maybe I have done something wrong when i installed SQL Express or v c# Express?

I have went over every thing i can find over and over,, to no resolve.

Flustered

Tom

|||

Anyone else have any ideals?

Totally lost and stuck

Tom

|||

Hi Tom,

Check out my answer at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=690061&SiteID=1, it will explain why you can't see your database.

Mike

|||

Good Day Mike,

Just wanted to say thanks for the input and info. I am still working to resolve this issue, just been slow as I had some health issues.

Thanks again, and I'll let ya know later today if I can get a resolution using this info.

Regards

Tom

|||

Hey Mike.

I just wanted to let you know that I tried doing what was instructed in the other post.

It has not resolved my issue, I still cant use the manager to locate/attatch the dbase to work with its properties.

However its not a major issue, I am using a back door in Visual to make my relations and FK settings, even if I cant go through the one step because of the permissions error, there working all the same.

Regards

Tom

|||Here is my solution to this problem. I had this same issue. After searching this forum I had the cues I needed to correct the issue but none of the suggested methods worked. I think the problem stemed from the fact that I had renamed the primary windows user account to my name (the original Owner account left from the OS install). In the folder structure some of the folders were still titled "Owner" but further down the tree the new name was used. So this account was previously "Owner" but had been changed to my name. The solution was the create a new Windows account, all of the folders had the new name associated with them. Then I recreated the database and the problem accessing the database diagram didn't reoccur. I hope this helps. Fyi: I am using Visual Basic 2005 Express but I think it is the same issue.|||

There are two options to resolve the dbo owner issue, one is to set a new owner as described in one of the above posts (and in some situations doe not work) and the other is to change the compatability level of your database to "SQL Server 2005 (90)".

In order to do so you should right-click the database in the Management Studio; and then select:

Properties --> Options --> Change "Compatibility level" to "SQL Server 2005 (90)"

Good luck

- Gal Cohen

dbo permissions

Hi!
is there a way to revoke "alter table" permission to db_owner role?
Thanks, in advance!
According to books on-line, you cannot change privileges of the fixed
database roles.
If possible, can you move the user to a user-defined database role with all
object privileges granted? This what we do in our shop. This will
eliminate the possibility that objects cannot be altered, dropped or created
on the fly..
"Francisco Castillo" <anonymous@.discussions.microsoft.com> wrote in message
news:2DC252B0-5755-4621-91E5-590D6644A30E@.microsoft.com...
> Hi!
> is there a way to revoke "alter table" permission to db_owner role?
> Thanks, in advance!
|||Hi,
I suggest you to assign the user the below database roles,
db_datareader
db_datawriter
after that give him the below previlages using a single grant command;
grant create procedure,create table,create view,create function to <User
name>
Thanks
Hari
MCDBA
"Eddy White" <1sg@.onewest.net> wrote in message
news:#fwdo24FEHA.2768@.tk2msftngp13.phx.gbl...
> According to books on-line, you cannot change privileges of the fixed
> database roles.
> If possible, can you move the user to a user-defined database role with
all
> object privileges granted? This what we do in our shop. This will
> eliminate the possibility that objects cannot be altered, dropped or
created
> on the fly..
> "Francisco Castillo" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2DC252B0-5755-4621-91E5-590D6644A30E@.microsoft.com...
>

dbo permissions

Is it possible for the dbo account to have a login to an SQL server?
If so how can that be discontinued?dbo is a special user account that exists in every database
but there isn't a login named dbo.
The login that owns the database is has the name DBO in that
database.
It's easier to not think of dbo as an user account as it
doesn't have a login and doesn't work like a typical user in
a database.
One other note on the login and hope this doesn't confuse
things but dbo isn't a login based on the special account
that exists in all databases. By default and generally on
most SQL Servers there is no login dbo. In theory you can
create a SQL login named dbo but it's not the same thing.
isn't a good practice and confuses things quite a bit. I saw
one place do that before...not a good thing to create
something like that.
-Sue
On Wed, 21 Sep 2005 13:39:08 -0400, "robertcp"
<rplance@.bmhsc.org> wrote:

>Is it possible for the dbo account to have a login to an SQL server?
>If so how can that be discontinued?
>|||To add to Sue's response, it is possible to create a SQL login that is used
only for database ownership and then change database ownership to that
login. Only that login will map to the 'dbo' user. However, note that
sysadmin role members are 'dbo' in all databases.
USE MyDatabase
DECLARE @.StrongPassword sysname
SET @.StrongPassword = NEWID()
EXEC sp_addlogin 'DatabaseOwner', @.StrongPassword
EXEC sp_changedbowner 'DatabaseOwner'
Hope this helps.
Dan Guzman
SQL Server MVP
"robertcp" <rplance@.bmhsc.org> wrote in message
news:eUQ9fNtvFHA.1988@.TK2MSFTNGP10.phx.gbl...
> Is it possible for the dbo account to have a login to an SQL server?
> If so how can that be discontinued?
>

dbo permissions

Hi!
is there a way to revoke "alter table" permission to db_owner role?
Thanks, in advance!According to books on-line, you cannot change privileges of the fixed
database roles.
If possible, can you move the user to a user-defined database role with all
object privileges granted? This what we do in our shop. This will
eliminate the possibility that objects cannot be altered, dropped or created
on the fly..
"Francisco Castillo" <anonymous@.discussions.microsoft.com> wrote in message
news:2DC252B0-5755-4621-91E5-590D6644A30E@.microsoft.com...
> Hi!
> is there a way to revoke "alter table" permission to db_owner role?
> Thanks, in advance!|||Hi,
I suggest you to assign the user the below database roles,
db_datareader
db_datawriter
after that give him the below previlages using a single grant command;
grant create procedure,create table,create view,create function to <User
name>
Thanks
Hari
MCDBA
"Eddy White" <1sg@.onewest.net> wrote in message
news:#fwdo24FEHA.2768@.tk2msftngp13.phx.gbl...
> According to books on-line, you cannot change privileges of the fixed
> database roles.
> If possible, can you move the user to a user-defined database role with
all
> object privileges granted? This what we do in our shop. This will
> eliminate the possibility that objects cannot be altered, dropped or
created
> on the fly..
> "Francisco Castillo" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2DC252B0-5755-4621-91E5-590D6644A30E@.microsoft.com...
>