SQL Enterprise Manager Question

microsoft.public.sqlserver.server

5 May 2005 2:32 PM

Nancy Kafer

I have a user that is defined on my SQL Server (not through an AD or SQL
group). The only reason for the user being defined this way is because they
need access to SQL Enterprise Manager to create views and stored procedures
in our test databases and they were having problems registering the SQL
server instance when they were only given rights to the database through AD
group membership. On our production server this user been defined in the
security section of the SQL Server instance however they have no authority
to create objects. We recently discovered that this user was able to rename
tables on the production server. Looking at the security I don't see where
he should have this authority.

Does he have authority to rename objects by virtue of being defined
individually instead of via group membership in the users section on the
database?

Thanks.

Nancy

5 May 2005 4:04 PM

Peter Nolan

Permissions are funny beasts, well they make me laugh anyway.

There are a couple of levels you can have a look at.

Silly statement first... The user is has access to the production database
and has the permission to change it.

The user is in a group in the database that has permission to change it.

Ok then server rights, rather than database rights.

The user has server rights to change the database, or the user is in a group
that allows database change.

Any one of those would do it, you will have to go though each option.

Let me tell you a little story of a time long ago...
We were doing testing and wanted to restrict one of out DBA's to just
database rather than server access. So we dutifully took her out of the
system administrator, added her to the database and left with a warm cosy
feeling of a job well done. Unfortunatly she could still enter different
databases on the server and modify tables. After 3 head scratching hours we
found out (unknown to her) she was a member of the Local Administrator group,
which in turn was database system administror.

Anyway have fun and remember its just a job :D

Peter

Show quoteHide quote

"Nancy Kafer" wrote:

> I have a user that is defined on my SQL Server (not through an AD or SQL
> group). The only reason for the user being defined this way is because they
> need access to SQL Enterprise Manager to create views and stored procedures
> in our test databases and they were having problems registering the SQL
> server instance when they were only given rights to the database through AD
> group membership. On our production server this user been defined in the
> security section of the SQL Server instance however they have no authority
> to create objects. We recently discovered that this user was able to rename
> tables on the production server. Looking at the security I don't see where
> he should have this authority.
>
> Does he have authority to rename objects by virtue of being defined
> individually instead of via group membership in the users section on the
> database?
>
> Thanks.
>
> Nancy
>
>
>

Are all your drivers up to date? click for free checkup

9 May 2005 3:03 PM

Nancy Kafer

Thanks for the advice Peter. I looked at the local groups on the machine but
didn't see any AD groups or users defined as local administrators (or any
other group on the local machine). I verified the AD groups the user is in
to make sure he wasn't in an AD group that would have more permissions and
he's not. The user is defined in SQL with database access of public and is a
member of a database role that also has access to the database. I also went
back to verify the server roles. This user (or any of the roles/groups he's
a member of) doesn't have any server role membership. The database roles he
is a member of don't have any elevated privileges.

I'm at a loss on what to check next. Would he be able to rename tables in a
database through Enterprise Manager just by virtue of being defined as an
individual database user instead of being a user via group membership?

Thanks.

Nancy

Show quoteHide quote

"Peter Nolan" <PeterNo***@discussions.microsoft.com> wrote in message
news:49DE6CFC-AB7E-4757-8742-5FA534D9C622@microsoft.com...
> Permissions are funny beasts, well they make me laugh anyway.
>
> There are a couple of levels you can have a look at.
>
> Silly statement first... The user is has access to the production database
> and has the permission to change it.
>
> The user is in a group in the database that has permission to change it.
>
> Ok then server rights, rather than database rights.
>
> The user has server rights to change the database, or the user is in a
group
> that allows database change.
>
> Any one of those would do it, you will have to go though each option.
>
> Let me tell you a little story of a time long ago...
> We were doing testing and wanted to restrict one of out DBA's to just
> database rather than server access. So we dutifully took her out of the
> system administrator, added her to the database and left with a warm cosy
> feeling of a job well done. Unfortunatly she could still enter different
> databases on the server and modify tables. After 3 head scratching hours
we
> found out (unknown to her) she was a member of the Local Administrator
group,
> which in turn was database system administror.
>
> Anyway have fun and remember its just a job :D
>
> Peter
>
> "Nancy Kafer" wrote:
>
> > I have a user that is defined on my SQL Server (not through an AD or SQL
> > group). The only reason for the user being defined this way is because
they
> > need access to SQL Enterprise Manager to create views and stored
procedures
> > in our test databases and they were having problems registering the SQL
> > server instance when they were only given rights to the database through
AD
> > group membership. On our production server this user been defined in the
> > security section of the SQL Server instance however they have no
authority
> > to create objects. We recently discovered that this user was able to
rename
> > tables on the production server. Looking at the security I don't see
where
> > he should have this authority.
> >
> > Does he have authority to rename objects by virtue of being defined
> > individually instead of via group membership in the users section on the
> > database?
> >
> > Thanks.
> >
> > Nancy
> >
> >
> >