user permissions problem. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

user permissions problem.

Author

Topic

s4nj4y
Starting Member

5 Posts

Posted - 2005-09-06 : 05:03:25

hi,
i've just been added as a new user to the sql server database via my windows login details. my new user (spatel) has been given exactly the same permissions as some of the existing users (e.g. chap99) in terms of what i can see and do on various databases. problem is that chapp99 can create and alter tables, but for some reason i cannot, even though the DBA who set me up swears that we have the same permissions. i want to have the same rights as chapp99.

any idea how i can solve this problem?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-06 : 05:11:25

either your DBA is not telling you the truth ,
or
there are explicit denies on some permissions,
or
you're only set to db_ddladmin and you're trying to alter a table owned by another user like dbo

HTH

--------------------
keeping it simple...

s4nj4y
Starting Member

5 Posts

Posted - 2005-09-06 : 06:28:46

is there anyway to check if i have "explicit denies on some permissions" or that i'm set to "db_ddladmin"????

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-06 : 06:33:52

run this in the database concerned

exec sp_helpuser 'youraccount'

--------------------
keeping it simple...

s4nj4y
Starting Member

5 Posts

Posted - 2005-09-06 : 08:30:56

thanks jen,

i ran the command on "spatel" the got two rows returned (db_datareader, db_datawriter) which were the same as "chap99". i guess this means the dba was right and the accounts are the same. any idea what to do next? reinstall enterprise manager on my pc? delete and recreate my user (spatel) again???

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-06 : 08:58:17

"problem is that chapp99 can create and alter tables"....IN ALL databases or just particular databases? Remember user rights in database can be different from user rights in another DB.

s4nj4y
Starting Member

5 Posts

Posted - 2005-09-06 : 09:45:04

chap99 can create and alter tables in the cpnet database, whilst i can't. chap99 and spatel look exactly the same using sp_helpuser, so i'm confused as to whats going wrong. i must be missing something...i just don't know what!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-06 : 11:11:11

Compare permissions for any 'Guest groups / NTworkgroups' that you belong to....or that SQL is assigning you (or your amte) into. the difference must be in there somewhere....

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-07 : 02:05:41

db_datareader and db_datawriter will not allow you to alter tables

you should be a memeber of db_ddladmin or db_owner to do this

chap99 might have sa privilege then?

quote:
Originally posted by s4nj4y

thanks jen,

i ran the command on "spatel" the got two rows returned (db_datareader, db_datawriter) which were the same as "chap99". i guess this means the dba was right and the accounts are the same. any idea what to do next? reinstall enterprise manager on my pc? delete and recreate my user (spatel) again???

--edit
btw, he's probably
(a) lying
(b) giving you a hard time
(c) doesn't honestly know what he's doing