Permission

Hi,
I have a database user in my SQL-server (a server I use for test and
development). This user has some select and update permissions on some
tables. Is it possible to make a script or anything else so that the databas
e
administrator for the original database doesnt have to edit all permissions
one by one?
Thanks
juliaIt's really up to you which permissions you want to grant in the copied
database. You can script things according to what you want and then run the
script after restoring the DB. Did you have something specific in mind?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:D5E1A963-DE6C-455C-9B45-E62B516A9473@.microsoft.com...
Hi,
I have a database user in my SQL-server (a server I use for test and
development). This user has some select and update permissions on some
tables. Is it possible to make a script or anything else so that the
database
administrator for the original database doesnt have to edit all permissions
one by one?
Thanks
julia|||Hi Tom,
Well, I would like to script only the user and the permission of that user.
Then I would like to send the script to the database administrator for him t
o
run the script.
I have tried to do like this:
In the SQL server I have choosen All tasks -> Generate SQL Script... ->
Options. Then I marked the "Script database", "Script database users and
database roles" and "Script object-level permissions".
This gave me a script that I can use but I miss the Table Permissions
(Select, Update, Insert, Delete, Dri) and the Procedure permissions (Exec).
This is what I need...
Thanks
Julia
"Tom Moreau" wrote:

> It's really up to you which permissions you want to grant in the copied
> database. You can script things according to what you want and then run t
he
> script after restoring the DB. Did you have something specific in mind?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:D5E1A963-DE6C-455C-9B45-E62B516A9473@.microsoft.com...
> Hi,
> I have a database user in my SQL-server (a server I use for test and
> development). This user has some select and update permissions on some
> tables. Is it possible to make a script or anything else so that the
> database
> administrator for the original database doesnt have to edit all permission
s
> one by one?
> Thanks
> julia
>|||If you go that route, you need to have it script the objects. It won't
script permissions unless objects have been scripted, too. Now, if you want
to do this programmatically, you can run:
EXEC sp_helprotect NULL, NULL, NULL, 'o'
Save the results into a temp table and then generate the appropriate
statements from it. Save the output and then run the script on the target.
That said, if the source and target DB's are in the same SQL Server
instance, then you don't have to worry about the permissions. If they're on
different instances, then you'd have to run sp_change_users_login to synch
the users and their logins. The permissions wouldn't need to be changed -
unless you needed different security between source and target.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:A083A0C5-C66A-4FD0-9B30-799777C71D53@.microsoft.com...
Hi Tom,
Well, I would like to script only the user and the permission of that user.
Then I would like to send the script to the database administrator for him
to
run the script.
I have tried to do like this:
In the SQL server I have choosen All tasks -> Generate SQL Script... ->
Options. Then I marked the "Script database", "Script database users and
database roles" and "Script object-level permissions".
This gave me a script that I can use but I miss the Table Permissions
(Select, Update, Insert, Delete, Dri) and the Procedure permissions (Exec).
This is what I need...
Thanks
Julia
"Tom Moreau" wrote:

> It's really up to you which permissions you want to grant in the copied
> database. You can script things according to what you want and then run
> the
> script after restoring the DB. Did you have something specific in mind?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:D5E1A963-DE6C-455C-9B45-E62B516A9473@.microsoft.com...
> Hi,
> I have a database user in my SQL-server (a server I use for test and
> development). This user has some select and update permissions on some
> tables. Is it possible to make a script or anything else so that the
> database
> administrator for the original database doesnt have to edit all
> permissions
> one by one?
> Thanks
> julia
>|||Thanks!
"Tom Moreau" wrote:

> If you go that route, you need to have it script the objects. It won't
> script permissions unless objects have been scripted, too. Now, if you wa
nt
> to do this programmatically, you can run:
> EXEC sp_helprotect NULL, NULL, NULL, 'o'
> Save the results into a temp table and then generate the appropriate
> statements from it. Save the output and then run the script on the targe
t.
> That said, if the source and target DB's are in the same SQL Server
> instance, then you don't have to worry about the permissions. If they're
on
> different instances, then you'd have to run sp_change_users_login to synch
> the users and their logins. The permissions wouldn't need to be changed -
> unless you needed different security between source and target.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Julia" <Julia@.discussions.microsoft.com> wrote in message
> news:A083A0C5-C66A-4FD0-9B30-799777C71D53@.microsoft.com...
> Hi Tom,
> Well, I would like to script only the user and the permission of that user
.
> Then I would like to send the script to the database administrator for him
> to
> run the script.
> I have tried to do like this:
> In the SQL server I have choosen All tasks -> Generate SQL Script... ->
> Options. Then I marked the "Script database", "Script database users and
> database roles" and "Script object-level permissions".
> This gave me a script that I can use but I miss the Table Permissions
> (Select, Update, Insert, Delete, Dri) and the Procedure permissions (Exec)
.
> This is what I need...
> Thanks
> Julia
> "Tom Moreau" wrote:
>
>