Permissions on a database

We are setting up a database for an outside user to access. We only want
them to have access to this database and nothing else.
At the moment, we want them to have full access to the database (create
tables, delete tables, look at data, update data, create and modify stored
procedures etc.) and any objects they create. They are building an Web
Application for us and we don't want to restrict them while they are
building it.
But we don't want them granting access or revoking access to anyone
(especially not me - I don't think they can since I am the sysadmin). I
later want to be able to restrict their access and limit access to sensitive
information in the database.
I thought about initially setting them up with db_owner access.
Will this prevent me from restricting them later?
If they create the tables, will they still have full access to the tables
(since they were db_owner when they created them)?
Thanks,
Tom.I would suggest giving them a Windows Authenticated Account and then mapping
that account as the dbo_owner in the database you specify.
If they are good, they will create their objects with the dbo.ObjectName
prefix. If they are not good, then you will end up with objects that look
like Rick.Table1.
You could do and sp_addalias and mark them as dbo in the database.
This should do what you want it to. By using the Windows account, you also
get the benefits of restricting everything else that they might wish to do
on your server as well as evaluate log in times, log outs, minimum password
lengths and so forth.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
>I would suggest giving them a Windows Authenticated Account and then
>mapping
> that account as the dbo_owner in the database you specify.
>
But then could I down grade them later, or would they always have dbo access
to objects they created?
> If they are good, they will create their objects with the dbo.ObjectName
> prefix. If they are not good, then you will end up with objects that
> look
> like Rick.Table1.
Would this be a problem for me down the line?
> You could do and sp_addalias and mark them as dbo in the database.
What does that do?
> This should do what you want it to. By using the Windows account, you
> also
> get the benefits of restricting everything else that they might wish to do
> on your server as well as evaluate log in times, log outs, minimum
> password
> lengths and so forth.
I didn't want to give them access to the Windows server at all. That was
why I was having them do Sql Server Authentication. Do I also have to give
them Windows Access also? If that is the case, I why would anyone use the
Sql Server Authentication anyway?
I am just trying to figure out the best way to do this.
Thanks,
Tom.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Can't you let them develop with full access now... and once they are done..
you can change the pwd for that login. That way they will not have complete
access. And for their limited acesss, create a new read only login for their
future use.
Thanks
GYK
"tshad" wrote:
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:OYOEMjttEHA.2876@.TK2MSFTNGP14.phx.gbl...
> >I would suggest giving them a Windows Authenticated Account and then
> >mapping
> > that account as the dbo_owner in the database you specify.
> >
> But then could I down grade them later, or would they always have dbo access
> to objects they created?
> > If they are good, they will create their objects with the dbo.ObjectName
> > prefix. If they are not good, then you will end up with objects that
> > look
> > like Rick.Table1.
> Would this be a problem for me down the line?
> >
> > You could do and sp_addalias and mark them as dbo in the database.
> What does that do?
> >
> > This should do what you want it to. By using the Windows account, you
> > also
> > get the benefits of restricting everything else that they might wish to do
> > on your server as well as evaluate log in times, log outs, minimum
> > password
> > lengths and so forth.
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
> I am just trying to figure out the best way to do this.
> Thanks,
> Tom.
> >
> >
> > HTH
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>
>|||> > If they are good, they will create their objects with the dbo.ObjectName
> > prefix. If they are not good, then you will end up with objects that
> > look
> > like Rick.Table1.
> Would this be a problem for me down the line?
Yes! dbo should own all of the objects in the database. Period.
> > You could do and sp_addalias and mark them as dbo in the database.
> What does that do?
It maps them into the database as a dbo rather than as their UserName who is
a member of the db_owners role.
If they are mapped as the dbo, they can do things like: CREATE TABLE Frog
This will make it dbo.Frog by default.
If they are mapped in as a User who is a member of db_owners role, then the
same CREATE TABLE Frog would come back as
UserName.Frog rather than dbo.Frog.
> > This should do what you want it to. By using the Windows account, you
> > also
> > get the benefits of restricting everything else that they might wish to
do
> > on your server as well as evaluate log in times, log outs, minimum
> > password
> > lengths and so forth.
> I didn't want to give them access to the Windows server at all. That was
> why I was having them do Sql Server Authentication. Do I also have to
give
> them Windows Access also? If that is the case, I why would anyone use the
> Sql Server Authentication anyway?
If you don't give them access to the Windows computer how do you expect them
to connect to the SQL Server? The SQL Authentication is used to allow SQL
Server to authenticate access to SQL Server rather than Windows
authenticating access to SQL Server.
> I am just trying to figure out the best way to do this.
> Thanks,
=)
Rick
> Tom.
> >
> >
> > HTH
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>