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 setup permissions by using field value?

Hello, I need some help. I am modifying a database that will be accessed by people in different parts of the world. I am trying to set user roles to show only records in each Location. Here is an example:

User Groups
Boston Users
New York Users
London Users

Database: machines
Table: tblmachines
fldname fldoperatingsystem fldgeocenter
--- -------- -----
foo.mydomain.com Windows 2000 BOSTON
loo.mydomain.com Windows NT 4.0 NEW YORK
moo.mydomain.com REDHAT LINUX LONDON

How do I configure user roles so that only users from Boston see Boston records, and users from New York see New York records etc etc? I am trying to key off the fldgeocenter field Any suggestions would be MOST welcome.User Roles doesn't help here.
The database roles does not help here.
The best way to filter the records horizontally is to implement stored procedures where "fldgeocenter" would be an input parameter. Use the value of that param. to filter the records in the WHERE statement.

Originally posted by dperrott
Hello, I need some help. I am modifying a database that will be accessed by people in different parts of the world. I am trying to set user roles to show only records in each Location. Here is an example:

User Groups
Boston Users
New York Users
London Users

Database: machines
Table: tblmachines
fldname fldoperatingsystem fldgeocenter
--- -------- -----
foo.mydomain.com Windows 2000 BOSTON
loo.mydomain.com Windows NT 4.0 NEW YORK
moo.mydomain.com REDHAT LINUX LONDON

How do I configure user roles so that only users from Boston see Boston records, and users from New York see New York records etc etc? I am trying to key off the fldgeocenter field Any suggestions would be MOST welcome.

How to set up permissions for a user, who need to work with SQL Server Agent

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Owner can execute job as well.|||

Thanks for the hint, but I do not want to have this user as an owner. The owner should be another account. This user (developer) should only have the capability to execute jobs we (DBA) provided to him. He should not see ALL jobs nor should he execute ALL jobs. I would like to set permissions in a way that he can start/stop/enable/disable ONE specific job.

Is there an easy way to realize it? Or maybe a more complicated way?

Regards

Norbert

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
>

How to set celldata and Permissions to Role

Hi All,

I know how to create a role and adding Members(users) to that role by using AMO class library,but i need some sample code like how we can set the database permissions to the role and how we can set the celldata to that particular role.If any one knows about this please send reply immediately.

it is very urgent.

Thanks in advance.

Hi,

I believe the Adventure Works sample AMO application has code for creating permissions (search for CubePermission or DimensionPermission in the code):

http://msdn2.microsoft.com/en-us/library/ms160736.aspx

In general, you can use this 'reverse engineering' trick to get AMO code:

- create the objects you want with the user interface (in BI Development Studio with wizard/editors or in SQL Management Studio); in this case, use the security dialog to setup the database/dimensions/cubes permissions

- then look at the XML: in BI Development Studio, right click on the cube item -> View Code and search for the CubePermission XML fragment to see the CubePermission, similar for DimensionPermission; in SQL Management you can right click on the cube, script Alter or Create, and then find the CubePermission XML fragment

- to be easier to read, copy the CubePermission in a separate file

- there is a 1-to-1 relationship between the AMO classes and properties and the XML, by reading the XML you will know what classes and properties to set; for example, the <CubePermission> element tells you to create in AMO a CubePermission object; the <Name> sub-element corresponds to the Name property

Adrian Dumitrascu