How to show roles with permissions to objects

Hello,

I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!

BruceHave you checked "sp_helprotect" , this one is permissions for all objects,

--

Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>

"Bruce" <deluxeinformation@.gmail.comwrote in message
news:1173802556.534695.194010@.p10g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hello,
>
I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!
>
Bruce
>

|||On Mar 14, 4:41 am, "Jack Vamvas" <DEL_TO_RE...@.del.comwrote:

Quote:

Originally Posted by

Have you checked "sp_helprotect" , this one is permissions for all objects,
>
--
>
Jack Vamvas
___________________________________
The latest IT jobs -www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
>
"Bruce" <deluxeinformat...@.gmail.comwrote in message
>
news:1173802556.534695.194010@.p10g2000cwp.googlegr oups.com...
>

Quote:

Originally Posted by

Hello,

>

Quote:

Originally Posted by

I am trying to write a script using SQL Server 2000 to list all of the
roles that have any permissions on a specified object (view, table,
sp, etc.). Essentially I am trying to script what is displayed when
one selects the 'list only users/user-defined database roles/public
with permissions to this object' option under 'manage permissions' in
EM but without showing individual users, only roles. I've looked at
the system sp's and the information_schema views but none of those
seem to give this information. Am I going to have to look directly at
the system tables? If anyone has a script that does this for a
specified object or can point me to more specific information on how
to do this I'd appreciate it. Thanks!

>

Quote:

Originally Posted by

Bruce

Thank you. I don't know how I overlooked that one. Sometimes I wish
BOL was laid out a little differently.

Bruce

how to show all the permission for a role

Hello,
I have an application database that has a role with specific permission to
each objects like table, proc, views.
There are about 200+ objects with different permission. some are select,
insert, ddr.
How can I write a SQL statement to show a report of this role with all the
different objects permissions.
I was able to go to role in Enterprise Manager and select permission.
I need an excel report. It would best it I can get the same results that I
see in EM using Query Analyzer.
You can use Northwind.
Any suggestions.Hi,
Execute the system stored procedure
sp_helprotect null,<Role Name>
Thanks
Hari
SQL Server MVP
"SQL Apprentice" wrote:

> Hello,
> I have an application database that has a role with specific permission to
> each objects like table, proc, views.
> There are about 200+ objects with different permission. some are select,
> insert, ddr.
> How can I write a SQL statement to show a report of this role with all the
> different objects permissions.
> I was able to go to role in Enterprise Manager and select permission.
> I need an excel report. It would best it I can get the same results that
I
> see in EM using Query Analyzer.
> You can use Northwind.
> Any suggestions.
>
>|||Thank you Hari.
"Hari Pra" <HariPra@.discussions.microsoft.com> wrote in message
news:FEB28171-446D-4EEB-8708-8BEFA4D3B0DA@.microsoft.com...
> Hi,
> Execute the system stored procedure
> sp_helprotect null,<Role Name>
> Thanks
> Hari
> SQL Server MVP
>
> "SQL Apprentice" wrote:
>
to
the
that I

how to setup db replication with objects owned by user other than dbo?

Hi, I was hoping someone could tell me how I am to replicate a database
between servers. The many of the tables, stored procedures and views are
owned by a user (e.g. USER1). The version running is SQL 2000. Lets take for
instance snapshot replication from Server1 to Server2.
Now I know that many people are having problems with replicating a database
and ending up realising that the tables, stored procs and views are now
owned by dbo. I want the replicated database objects owned by USER1. What do
I have to do? Do I:
1) create USER1 on the Server2,
2) and then start snapshot replication from Server1 assigning "Publication
properties > Articles > Article Defaults > Destination Table Owner" = USER1
?
3) then move across USER1's permissions to the replicated objects (not sure
how to proceed with this, I think i have to use ).
or do I:
1) create USER1 on the Server2,
2) use DTS to create empty table structures on Server2
3) and then assign USER1 the permissions
I am somewhat confused because USER1's permissions are dependent on the
tables being defined, and the tables are dependent on USER1 having ownership
on them. I dont know what to set up first.
Simply stated, how do I simply "set up a replication from scratch for a
database with objects owned by a user other than dbo"
Could anyone direct me?
Thanks!
PeterGenerate SQL Script of login,
Execute it on new server (create login + permissions +
add_role logins... )
then DTS the objects of the server (what you want) to the
other server...
>--Original Message--
>Hi, I was hoping someone could tell me how I am to
replicate a database
>between servers. The many of the tables, stored
procedures and views are
>owned by a user (e.g. USER1). The version running is SQL
2000. Lets take for
>instance snapshot replication from Server1 to Server2.
>Now I know that many people are having problems with
replicating a database
>and ending up realising that the tables, stored procs and
views are now
>owned by dbo. I want the replicated database objects
owned by USER1. What do
>I have to do? Do I:
>1) create USER1 on the Server2,
>2) and then start snapshot replication from Server1
assigning "Publication
>properties > Articles > Article Defaults > Destination
Table Owner" = USER1
>?
>3) then move across USER1's permissions to the replicated
objects (not sure
>how to proceed with this, I think i have to use ).
>or do I:
>1) create USER1 on the Server2,
>2) use DTS to create empty table structures on Server2
>3) and then assign USER1 the permissions
>I am somewhat confused because USER1's permissions are
dependent on the
>tables being defined, and the tables are dependent on
USER1 having ownership
>on them. I dont know what to set up first.
>Simply stated, how do I simply "set up a replication from
scratch for a
>database with objects owned by a user other than dbo"
>Could anyone direct me?
>Thanks!
>Peter
>
>
>.
>

How to set permissions for objects quickly

After I create a user (ref to database), then I need to assign 'select' &
'exec' rights at permissions for all objects. But there are over 1000
objects. How can I set the permissions quickly? Can I do it at query
analyzer?
Alternatively, what is the best way to setup this if want to add / rename
database username? Thanks.
"Pleo" <rx8@.hotmail.com> bl news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl
g...
> I'm not familiar sql. At enterprise server (sql2000) > security > logins >
> (want to change name here).
> Anyway, I guess it can't be changed there. Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
> referring to the login name
change[vbcol=seagreen]
> the name of a login or a
> though.
> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>SELECT permissions on all user tables and views can be assigned by adding
users to the db_datareader fixed database role. There is no such role for
executing procs but you can assign such permissions by creating your own
role and using a script like the one below to grant permissions on all
existing stored procedures:
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Pleo" <rx8@.hotmail.com> wrote in message
news:un0Y5Rw0FHA.1564@.tk2msftngp13.phx.gbl...
> After I create a user (ref to database), then I need to assign 'select' &
> 'exec' rights at permissions for all objects. But there are over 1000
> objects. How can I set the permissions quickly? Can I do it at query
> analyzer?
> Alternatively, what is the best way to setup this if want to add / rename
> database username? Thanks.
> "Pleo" <rx8@.hotmail.com> bl news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl
>
> g...
> change
>

How to set permissions for objects quickly

After I create a user (ref to database), then I need to assign 'select' &
'exec' rights at permissions for all objects. But there are over 1000
objects. How can I set the permissions quickly? Can I do it at query
analyzer?
Alternatively, what is the best way to setup this if want to add / rename
database username? Thanks.
"Pleo" <rx8@.hotmail.com> ¦b¶l¥ó news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl ¤¤
¼¶¼g...
> I'm not familiar sql. At enterprise server (sql2000) > security > logins >
> (want to change name here).
> Anyway, I guess it can't be changed there. Thanks.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
> > Can you explain what you mean by "change sql login username"? Are you
> referring to the login name
> > (in master) or the user name (in your database)? Anyhow, you cannot
change
> the name of a login or a
> > user, You will be able to rename a user in 2005, not sure about login,
> though.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Pleo" <rx8@.hotmail.com> wrote in message
> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
> > > As title, thanks!
> > >
> > >
> >
>
>SELECT permissions on all user tables and views can be assigned by adding
users to the db_datareader fixed database role. There is no such role for
executing procs but you can assign such permissions by creating your own
role and using a script like the one below to grant permissions on all
existing stored procedures:
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pleo" <rx8@.hotmail.com> wrote in message
news:un0Y5Rw0FHA.1564@.tk2msftngp13.phx.gbl...
> After I create a user (ref to database), then I need to assign 'select' &
> 'exec' rights at permissions for all objects. But there are over 1000
> objects. How can I set the permissions quickly? Can I do it at query
> analyzer?
> Alternatively, what is the best way to setup this if want to add / rename
> database username? Thanks.
> "Pleo" <rx8@.hotmail.com> ¦b¶l¥ó news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl
> ¤¤
> ¼¶¼g...
>> I'm not familiar sql. At enterprise server (sql2000) > security > logins
>> >
>> (want to change name here).
>> Anyway, I guess it can't be changed there. Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
>> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> > Can you explain what you mean by "change sql login username"? Are you
>> referring to the login name
>> > (in master) or the user name (in your database)? Anyhow, you cannot
> change
>> the name of a login or a
>> > user, You will be able to rename a user in 2005, not sure about login,
>> though.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> > Blog: http://solidqualitylearning.com/blogs/tibor/
>> >
>> >
>> > "Pleo" <rx8@.hotmail.com> wrote in message
>> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > > As title, thanks!
>> > >
>> > >
>> >
>>
>

How to set permissions for objects quickly

After I create a user (ref to database), then I need to assign 'select' &
'exec' rights at permissions for all objects. But there are over 1000
objects. How can I set the permissions quickly? Can I do it at query
analyzer?
Alternatively, what is the best way to setup this if want to add / rename
database username? Thanks.
"Pleo" <rx8@.hotmail.com> bl news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl
g...[vbcol=seagreen]
> I'm not familiar sql. At enterprise server (sql2000) > security > logins >
> (want to change name here).
> Anyway, I guess it can't be changed there. Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
> referring to the login name
change
> the name of a login or a
> though.
> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>
SELECT permissions on all user tables and views can be assigned by adding
users to the db_datareader fixed database role. There is no such role for
executing procs but you can assign such permissions by creating your own
role and using a script like the one below to grant permissions on all
existing stored procedures:
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Pleo" <rx8@.hotmail.com> wrote in message
news:un0Y5Rw0FHA.1564@.tk2msftngp13.phx.gbl...
> After I create a user (ref to database), then I need to assign 'select' &
> 'exec' rights at permissions for all objects. But there are over 1000
> objects. How can I set the permissions quickly? Can I do it at query
> analyzer?
> Alternatively, what is the best way to setup this if want to add / rename
> database username? Thanks.
> "Pleo" <rx8@.hotmail.com> bl news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl
> g...
> change
>