How to set rights in E.Manager Since it is a open book..

Hi,

For working with linked servers , Mr. Hills had replied like , one can
not see the login rights etc if the particular user is not in the
admin role in the Enter prise manager.

Since the E.Manager is a open book so that any one can access any
database , Do you explain me When the sql server E.manager ks the
user's login and password .

With thanks

RAGHUWhen you register a server in EM you specify an authentication mode.

If you choose Windows Authentication then the login information is taken
from your Windows domain login - you will have access only to the databases
to which your login has been granted permissions.

If you choose SQL Server Authentication then you can opt either to save the
login name and password in the registry or to prompt for a login name each
time you try to connect to the server in EM. Your level of access is
determined by the SQL Server login name supplied.

For maximum security use Windows Authentication or set the option to prompt
for a login name each time you connect. If you choose Windows Authentication
then also password protect your screen saver so that your PC is secure when
you are away.

Does that answer your question?

--
David Portas
----
Please reply only to the newsgroup
--

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 separate users in one database (MSSQL 2000)

I wonder how I can separate between different user's rights in the same
database?
In one database -i have user A and user B, how do I set the rights so
that user A can not access user B's objects and opposite? This rights
should be valid for all previously created objects and for objects
created later.
RegardsIf there is truly no interaction between the two sets of objects I question
why they are even in the same database? But there are several things you
can do. First remember that no one by default can access any objects unless
you give them permissions to do so. You can create a role for each set of
objects and add each user to the respective roles. Then only grant
permissions to those objects for that role that you want them to access.
This gives you the flexibility of adding other users later on as well. You
can also create the object with a particular owner to separate them but you
still need to set up the permissions appropriately. Check out GRANT and
DENY in BooksOnLine.
Andrew J. Kelly SQL MVP
<tormorteng@.gmail.com> wrote in message
news:1141309610.551856.52120@.i39g2000cwa.googlegroups.com...
>I wonder how I can separate between different user's rights in the same
> database?
> In one database -i have user A and user B, how do I set the rights so
> that user A can not access user B's objects and opposite? This rights
> should be valid for all previously created objects and for objects
> created later.
> Regards
>