December 13, 2007 at 3:52 am
Hi All,
I've got a problem where I'm trying to lock down our SQL 2005 servers to our developers (basically not make them sysadmins), everything is OK except that they need to be able to run scripts into the databases using OSQL.
The problem I've encountered is... If you are not a sysadmin and run a create table command via OSQL (where you are a dbo on the database) it creates the tables as .
This is different behavior than if the same user runs exactly the same create table command in sql query window (it creates it as dbo. )
Does anybody know a way around this? Or alternatively do you think that using SQLCMD instead of OSQL will resolve this as this is also an option, but would just require a larger change of our processes.
Thanks in advance.
Stu
sorry - original posted this in incorrect forum.
December 13, 2007 at 5:08 am
If they connect with the same credentials using OSQL and SSMS, it should work in the same way.
What's the default schema for the user ?
SQLCMD has more features than OSQL, but the TSQL statements should work exactly the same.
Anyway, best practise is to always specify the schema for the object you are creating.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 13, 2007 at 5:17 am
Hi ALZDBA,
Default schema is dbo, and am specifying schema of dbo in create table command. Everything else seems to work same in OSQL and SSMS but only way to resolve this is to give user running OSQL sysadmin rights and then it works fine.
Thanks,
December 13, 2007 at 8:01 am
And this guy is connecting using windows authentication ?
Or is he using SQL authentication ?
Which db groups is this user member off ?
(ddl_admin, ...)
If this user gets auth via a windowsgroup, sqlserver cannot allocate a default schema.
If you create objects specifying the schema, it should always work if that user has the right to create objects in that schema.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 13, 2007 at 8:46 am
User connecting using Windows Authentication, user is member of db_owner so database permissions should not be an issue.
User does get permissons via a Windows Group but this doesn't stop it using dbo schema when he executes via SSMS.
Just strange that I can get user to use SSMS using same credentials and then run exactly same command and works fine, it appears to be the way OSQL handles the specifing of schema dbo (or doesn't handle it). If I give him sysadmin rights it works OK but I presume that is because everything a sysadmin creates has to have schema dbo.
Would be ideal if there was a way to force any table that is created to use dbo schema, but I don't know if this is possible.
December 14, 2007 at 1:15 am
So he gets an error when executing :
create table dbo.mytesttable (col1 int not null)
:unsure:
Very strange.
How many schema are defined in the db ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 14, 2007 at 2:28 am
It doesn't error it just creates the table with username as schema which is not very useful, doesn't seem to pass through the specifying of schema.
There are just the standard schemas plus the user I've been testing with.
I've tested this on numerous databases on different servers (even tested on SQL 2000 with brand new database) and exactly the same behaviour occurs on them all.
I think I'm just going to have to try SQLCMD and hope this passes through the specifying of the schema as you'd expect.
Thanks
December 14, 2007 at 2:46 am
it just creates the table with username as schema
IMO the behaviour you describe is applicable when you run a :
create table mytable (col1) ;
in that case no schema is provided and it will try to use the schema "username".
If that is abscent it will use the "dbo" schema.
There is no implicit schema creation in sql2005.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply