USer ID Setup

Question

USer ID Setup

Sanjay Mishra

Valued Member

Points: 53
More actions
August 3, 2004 at 11:10 am

#161858

I have a userid who is doing DML like Select/insert/Delete. Now he need to have create table priviliges and so I want that
--> He can only been able to create Table and not able to drop the table.
--> When he create table, it will be created as dbo.tablename. I can tell hime to create them explictly using dbo.tablename but if he didn't mention then I don't want that it should be created in his own schema.
Thanks and I really if any expert can reply at his/her earliest convenience

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 1

You can give create permission, but drop goes to teh table owner. I'd give them create under their user name, so they'd create tables as

bill.mytable

You can use sp_changeobject_owner to move it to dbo.

Sanjay Mishra Valued Member Points: 53 More actions · Answer 2

Thanks Steve for you greate response. Actually I am new to Sql server and earlier working with Oracle. In Oracle I can grant create table priviliges and revike Drop table priviliges as well as use Product Profile option to restrict user to run DROP command in Oracle SQLPLUS.

Now I like you option but the only problem is that how I can check if the new object is been created so as to change the owner to dbo. Actually one user to create a few table at the month closing that is been populated by data from external excel spreadsheet and then been used by other user for reporting purpose. As there will be different table for each month as well as all other had to access the table, I want to automate the task. I really appreciate you expert comment on this issue

Thanks in Advance for your timely help

Sanjay

Sanjay Mishra Valued Member Points: 53 More actions · Answer 3

Steve

CAn you suggest if I can give the user some privilige so that whenever he is creating the new table it will automatically be created with dbo.table and not with his bill.table

This is very urgent requirement and user claim that he used to have this before and I was moved to sqlserver group too handle this request

THanks

Sanjay Mishra Valued Member Points: 53 More actions · Answer 4

I was checking one document somewhere on the web and then had given the sysadmin role to the user and so now if I connect as user ABC and create the table, it is automatically created under DBO.

But I want to avoid this sysadmin role and so it look like there might be some other way known to you all sql server expert as I am newbie anc cannot comment.

TIA

S