Newly created SQL login can't read any databases - can I give it permissions to read all databases

9 Jul 2009 5:04 PM

franklinhu

I have observed that if you create a new SQL login user and only grant
it the public role, that this user cannot do selects on any database
on the server - which makes it pretty useless. To get around this, I
can assign the sysadmin role to this user and then I immediately have
read/write access to all the databases on the server. But this is a
big security problem in that I only want my SQL user to have SELECT
access to all databases.

I would like to be able to grant my SQL login user, the same wide
database access as the sysadmin, but with only limited rights on the
databases.

Based on what I have been able to read in the documentation, it is
impossible to do this without having to modify every database that I
want my new SQL login to access. The GRANT command is only able to
change permissions for a single database, not all the databases in the
system. So it seems that I have to create a user in each of my
databases, grant it the appropriate roles and then associate it with
my SQL login user.

I need to create new databases all the time which need to be accessed
by the SQL login user, so I would have to keep doing this grant/
asociate action every time I need to create a database.

Is there an easier way than this? This seems to basically force you to
run as sysadmin if you want to access all databases without having to
tweak every single one of them to be accessable by a non-sysadmin
account.

fhusqllogin

9 Jul 2009 5:12 PM

Rick Sawtell

Show quote Hide quote

"franklinhu" <frankli***@yahoo.com> wrote in message
news:08e26caa-ac04-45ee-8808-267a2e17eed7@d9g2000prh.googlegroups.com...

> I need to create new databases all the time which need to be accessed
> by the SQL login user, so I would have to keep doing this grant/
> asociate action every time I need to create a database.
>
> Is there an easier way than this? This seems to basically force you to
> run as sysadmin if you want to access all databases without having to
> tweak every single one of them to be accessable by a non-sysadmin
> account.
>
> fhusqllogin

You could modify the model database, add this SQL Login to the model
database and grant the appropriate permissions there. (datareader for
example).

Any new database you create will now have that login associated with it as
well as the permissions.

Rick Sawtell
MCT, MCSD, MCDBA, MCITPro

9 Jul 2009 5:32 PM

Linchi Shea

Why can't you just make granting database access and granting permissions
part of your create database procedrue?

Implementing what Rick suggested in the model database will enable the login
to access the newly created database via database level roles. If that is all
you need, it's good. But if you want more granular permissions, you still
have to work on the objects in that database anyway.

Linchi

Show quoteHide quote

"franklinhu" wrote:

> I have observed that if you create a new SQL login user and only grant
> it the public role, that this user cannot do selects on any database
> on the server - which makes it pretty useless. To get around this, I
> can assign the sysadmin role to this user and then I immediately have
> read/write access to all the databases on the server. But this is a
> big security problem in that I only want my SQL user to have SELECT
> access to all databases.
>
> I would like to be able to grant my SQL login user, the same wide
> database access as the sysadmin, but with only limited rights on the
> databases.
>
> Based on what I have been able to read in the documentation, it is
> impossible to do this without having to modify every database that I
> want my new SQL login to access. The GRANT command is only able to
> change permissions for a single database, not all the databases in the
> system. So it seems that I have to create a user in each of my
> databases, grant it the appropriate roles and then associate it with
> my SQL login user.
>
> I need to create new databases all the time which need to be accessed
> by the SQL login user, so I would have to keep doing this grant/
> asociate action every time I need to create a database.
>
> Is there an easier way than this? This seems to basically force you to
> run as sysadmin if you want to access all databases without having to
> tweak every single one of them to be accessable by a non-sysadmin
> account.
>
> fhusqllogin
>

9 Jul 2009 10:42 PM

John Bell

Show quote Hide quote

"franklinhu" <frankli***@yahoo.com> wrote in message
news:08e26caa-ac04-45ee-8808-267a2e17eed7@d9g2000prh.googlegroups.com...
>I have observed that if you create a new SQL login user and only grant
> it the public role, that this user cannot do selects on any database
> on the server - which makes it pretty useless. To get around this, I
> can assign the sysadmin role to this user and then I immediately have
> read/write access to all the databases on the server. But this is a
> big security problem in that I only want my SQL user to have SELECT
> access to all databases.
>
> I would like to be able to grant my SQL login user, the same wide
> database access as the sysadmin, but with only limited rights on the
> databases.
>
> Based on what I have been able to read in the documentation, it is
> impossible to do this without having to modify every database that I
> want my new SQL login to access. The GRANT command is only able to
> change permissions for a single database, not all the databases in the
> system. So it seems that I have to create a user in each of my
> databases, grant it the appropriate roles and then associate it with
> my SQL login user.
>
> I need to create new databases all the time which need to be accessed
> by the SQL login user, so I would have to keep doing this grant/
> asociate action every time I need to create a database.
>
> Is there an easier way than this? This seems to basically force you to
> run as sysadmin if you want to access all databases without having to
> tweak every single one of them to be accessable by a non-sysadmin
> account.
>
> fhusqllogin

Hi

In general you will need to grant someone the most restrictive permissions
possible to get the job done, and usually having access to every database is
not a common requirement and the security model does not easily allow that.
You could change the model database to have a role with the required
permissions, so any new database will have the role, although this may not
be something assume will not disappear on an service pack or patch. Having a
script to add roles to new databases or add users to roles, may need to be
re-run when you create a new database object, database or user or you could
have DDL Triggers that will automatically do this when the events fire.

John