Wednesday, March 21, 2012

Permissions of Logins

I made a new login for my database. In the database access
tab i only checked my database as permit and gave the login
no server roles. But when I log on to the SQL Server with
that login, I can see all Databases and their tables on the
server. Why is that?
Thanks, Rainer.
hi Rainer,
"Rainer Halanek" <anonymous@.discussions.microsoft.com> ha scritto nel
messaggio news:03e001c4b697$37083c20$a401280a@.phx.gbl
> I made a new login for my database. In the database access
> tab i only checked my database as permit and gave the login
> no server roles. But when I log on to the SQL Server with
> that login, I can see all Databases and their tables on the
> server. Why is that?
>
if your login only is associated to 1 database users and for only 1
database, you should get
err 916 Server user 'x' is not a valid user in database 'db_name'
raised by Enterprise Manager...
please verify the login is not member of a particular server role
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
You aksed if the login is associated to a database user. I
don't really understand that. I thougth that the login is a
database user. I associated it with one database (the only
one where I checked the permit flag). Maybe I did something
wrong?
Thanks, rainer.

>--Original Message--
>hi Rainer,
>"Rainer Halanek" <anonymous@.discussions.microsoft.com> ha
scritto nel
>messaggio news:03e001c4b697$37083c20$a401280a@.phx.gbl
>if your login only is associated to 1 database users and
for only 1
>database, you should get
>err 916 Server user 'x' is not a valid user in database
'db_name'
>raised by Enterprise Manager...
>please verify the login is not member of a particular
server role
>--
>Andrea Montanari (Microsoft MVP - SQL Server)
>http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
>DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
>(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE
2000 a visual
>interface)
>-- remove DMO to reply
>.
>
|||Rainer,
You will still be able to see all the databases on the server. You will
also still have access to anything the public role has access to on the
system databases. You won't be able to actually open up any of the stored
procedures/tables/etc on the server other databases though. I would
recommened deleting the guest account and restricting the public role
though. You can read more about SQL Server security at www.sqlsecurity.com.
<anonymous@.discussions.microsoft.com> wrote in message
news:046c01c4b83f$6e781370$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Andrea,
> You aksed if the login is associated to a database user. I
> don't really understand that. I thougth that the login is a
> database user. I associated it with one database (the only
> one where I checked the permit flag). Maybe I did something
> wrong?
> Thanks, rainer.
> scritto nel
> for only 1
> 'db_name'
> server role
> 2000 a visual
|||This is also something that changes with SQL Express and with SQL Server
2005. You will no longer be able to see objects that you have no access to.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Derrick Leggett" <derrickleggett@.yahoo.com> wrote in message
news:eh1l91RuEHA.3496@.TK2MSFTNGP10.phx.gbl...
> Rainer,
> You will still be able to see all the databases on the server. You will
> also still have access to anything the public role has access to on the
> system databases. You won't be able to actually open up any of the stored
> procedures/tables/etc on the server other databases though. I would
> recommened deleting the guest account and restricting the public role
> though. You can read more about SQL Server security at
> www.sqlsecurity.com.
> <anonymous@.discussions.microsoft.com> wrote in message
> news:046c01c4b83f$6e781370$a501280a@.phx.gbl...
>

No comments:

Post a Comment