Hi,
Is there a way to automate a process that export the database strucuture once a day !
All the objects - Tables, Indexes, Procedures, Views Etc..
Any Help I apreciate !
Thank's
You could run a sql agent job that uses SQL-DMO to script your database.
Here is an article that I wrote that might help:
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carrasco" <Carrasco@.discussions.microsoft.com> wrote in message
news:5964A3E3-E149-4E4D-812D-B6B62FE9C878@.microsoft.com...
> Hi,
> Is there a way to automate a process that export the database strucuture
once a day !
> All the objects - Tables, Indexes, Procedures, Views Etc..
> Any Help I apreciate !
> Thank's
>
sql
Showing posts with label objects. Show all posts
Showing posts with label objects. Show all posts
Thursday, March 29, 2012
Sunday, March 11, 2012
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.com
Robert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com
|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.com
Robert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com
|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitl
y
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, o
r
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put
a
> check mark (such as select, insert, update, delete) for access to a table,
or
> Exec for a stored procedure for the user to have those specified permissio
ns
> on the object, because when the user is granted the database owner role, a
ll
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitl
y
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, o
r
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put
a
> check mark (such as select, insert, update, delete) for access to a table,
or
> Exec for a stored procedure for the user to have those specified permissio
ns
> on the object, because when the user is granted the database owner role, a
ll
> the check boxes are blank.
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
--
Message posted via http://www.sqlmonster.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.sqlmonster.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>> If I set up a user with a database role of dbo, does that automatically
>> give
>[quoted text clipped - 3 lines]
>> go in and put an "X" in the execute permission checkbox next to the stored
>> procedure?
--
Message posted via http://www.sqlmonster.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via SQLMonster.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
--
Message posted via http://www.sqlmonster.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.sqlmonster.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>> If I set up a user with a database role of dbo, does that automatically
>> give
>[quoted text clipped - 3 lines]
>> go in and put an "X" in the execute permission checkbox next to the stored
>> procedure?
--
Message posted via http://www.sqlmonster.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via SQLMonster.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
Database Roles
Hi,
My understanding of db roles is that you don't have to explicitly set
permissions on objects as they role should give them this.
For example is I assign db_datareader to a user, then they automatically
have "Select" permissions on all tables
Is this right or am I going mad
SimonYes, assign a role to a user and the user will get the permissions defined
by the roles. Just like groups in any product. :-)
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> My understanding of db roles is that you don't have to explicitly set
> permissions on objects as they role should give them this.
> For example is I assign db_datareader to a user, then they automatically
> have "Select" permissions on all tables
> Is this right or am I going mad
> Simon
>|||Strange
I have assigned this user to the role, but was still getting Select
permissions denied errors. What else could be wrong?
Simon
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> Yes, assign a role to a user and the user will get the permissions defined
> by the roles. Just like groups in any product. :-)
> --
> Tibor Karaszi
>
> "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My understanding of db roles is that you don't have to explicitly set
> > permissions on objects as they role should give them this.
> >
> > For example is I assign db_datareader to a user, then they automatically
> > have "Select" permissions on all tables
> >
> > Is this right or am I going mad
> >
> > Simon
> >
> >
>|||Perhaps you have assigned DENY permissions somehow? Deny is always stronger.
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23MJ8qj5oDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Strange
> I have assigned this user to the role, but was still getting Select
> permissions denied errors. What else could be wrong?
> Simon
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> > Yes, assign a role to a user and the user will get the permissions
defined
> > by the roles. Just like groups in any product. :-)
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> > news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > My understanding of db roles is that you don't have to explicitly set
> > > permissions on objects as they role should give them this.
> > >
> > > For example is I assign db_datareader to a user, then they
automatically
> > > have "Select" permissions on all tables
> > >
> > > Is this right or am I going mad
> > >
> > > Simon
> > >
> > >
> >
> >
>
My understanding of db roles is that you don't have to explicitly set
permissions on objects as they role should give them this.
For example is I assign db_datareader to a user, then they automatically
have "Select" permissions on all tables
Is this right or am I going mad
SimonYes, assign a role to a user and the user will get the permissions defined
by the roles. Just like groups in any product. :-)
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> Hi,
> My understanding of db roles is that you don't have to explicitly set
> permissions on objects as they role should give them this.
> For example is I assign db_datareader to a user, then they automatically
> have "Select" permissions on all tables
> Is this right or am I going mad
> Simon
>|||Strange
I have assigned this user to the role, but was still getting Select
permissions denied errors. What else could be wrong?
Simon
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> Yes, assign a role to a user and the user will get the permissions defined
> by the roles. Just like groups in any product. :-)
> --
> Tibor Karaszi
>
> "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My understanding of db roles is that you don't have to explicitly set
> > permissions on objects as they role should give them this.
> >
> > For example is I assign db_datareader to a user, then they automatically
> > have "Select" permissions on all tables
> >
> > Is this right or am I going mad
> >
> > Simon
> >
> >
>|||Perhaps you have assigned DENY permissions somehow? Deny is always stronger.
--
Tibor Karaszi
"Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
news:%23MJ8qj5oDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Strange
> I have assigned this user to the role, but was still getting Select
> permissions denied errors. What else could be wrong?
> Simon
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:3S5qb.32418$mU6.93605@.newsb.telia.net...
> > Yes, assign a role to a user and the user will get the permissions
defined
> > by the roles. Just like groups in any product. :-)
> >
> > --
> > Tibor Karaszi
> >
> >
> > "Simon McDermott" <simon.mcdermott@.bicsystems.com> wrote in message
> > news:%23Gi8MV5oDHA.1884@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > My understanding of db roles is that you don't have to explicitly set
> > > permissions on objects as they role should give them this.
> > >
> > > For example is I assign db_datareader to a user, then they
automatically
> > > have "Select" permissions on all tables
> > >
> > > Is this right or am I going mad
> > >
> > > Simon
> > >
> > >
> >
> >
>
Friday, February 17, 2012
Database poster
Does anyone know where to get posters for the
database/objects/infrastructure for MS SQL 2005?Here is the pdf version, this was also in SQL Server Magazine back in
December I believe
http://www.microsoft.com/downloads/...&displaylang=en
database/objects/infrastructure for MS SQL 2005?Here is the pdf version, this was also in SQL Server Magazine back in
December I believe
http://www.microsoft.com/downloads/...&displaylang=en
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks it looks great!!!!!!!
Tuesday, February 14, 2012
Database ownership
Hi
Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.
However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .
Thanksmay be a bug with sp_changedbowner, when done through EM it works.|||Originally posted by Paul Young
may be a bug with sp_changedbowner, when done through EM it works.
Thanks Paul.
I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?|||yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.|||Originally posted by Paul Young
yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.
Thanks again
However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
connecting via query analyzer using nt authentication.
What I'm looking for is to be able to get the database owned by
the nt group so that any table created by the users are owned by dbo
and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.
Any ideas?|||user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?|||Originally posted by Paul Young
user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?
Thanks for your patience. When a sql login owns a database then
all tables created are dbo.table rather than sqllogin.table
This is what I'm trying to achieve with the NT group .
When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).
I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.
I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .
Thanks again for your help Paul
Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.
However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .
Thanksmay be a bug with sp_changedbowner, when done through EM it works.|||Originally posted by Paul Young
may be a bug with sp_changedbowner, when done through EM it works.
Thanks Paul.
I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?|||yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.|||Originally posted by Paul Young
yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.
Thanks again
However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
connecting via query analyzer using nt authentication.
What I'm looking for is to be able to get the database owned by
the nt group so that any table created by the users are owned by dbo
and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.
Any ideas?|||user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?|||Originally posted by Paul Young
user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.
Can you move all the user objects to a holding db and try your tests again?
Thanks for your patience. When a sql login owns a database then
all tables created are dbo.table rather than sqllogin.table
This is what I'm trying to achieve with the NT group .
When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).
I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.
I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .
Thanks again for your help Paul
Subscribe to:
Posts (Atom)