Logins permissions for non-sa RESTORE?

I hope this is a nice fat ball that someone can knock out of the park...

We've recently started to upgrade our development servers from Win2k to Windows Server 2003. Naturally, the SQL Server boxes receive MS SQL Server 2000 SP3a. Our database users now cannot restore to their own databases.

We usually grant each login the 'dbcreator' server role (and hope the developers are too busy to realize everything else it allows). Each user's login is dbo in his/her database. Typically one developer will produce a reference dump file, and all the other developers load it as they need it.

If a user executes a restore, it trundles along happily until almost the end, spitting out an error. Here's a sample:

100 percent restored.
Processed 376 pages for database 'bobdb', file 'Data' on file 1.
Server: Msg 916, Level 14, State 1, Line 68
Server user 'bob' is not a valid user in database 'bobdb'.
Server: Msg 3013, Level 16, State 1, Line 68
RESTORE DATABASE is terminating abnormally.

Oh, and this worked with SQL Server 2000 (any patch) on Windows 2000 Server.

Developers reload their databases so often (and DBA resources are so spare) that routing all restores through a DBA will bring development to its knees. So, each developer must be able to restore dumps to their own database.

Help? Which server roles/permissions/GRANTs do we need to provide our developers with the power they need? (and not the powers they don't need!)If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.

[Books Online] RESTORE|||Our DBAs initially set up databases for each developer, so they should be appropriately configured at that point. Each developer login has the dbcreator role. Each developer's login owns the corresponding database.

GRANT CREATE DATABASE only applies to database users, not SQL Server logins. That is to say, the first sentence from the Online Books doesn't exactly make sense. (What user? in which database?)

Essentially, at some point during the restore, SQL Server forgets that the user doing the restore used to own the database in question, and in so doing it gets a bit confused.

If I create my own dump file, I can restore it to my account. Previously, I could restore a dump created by anyone else.

This is the only other clue I have. I executed this immediately after restoring my database from my dump file.

use bobdb
go
exec sp_changedbowner 'bob'
go

Server: Msg 15247, Level 16, State 1, Procedure sp_changedbowner, Line 15
User does not have permission to perform this action.

Previously I could execute this on my own databases (it's a no-op but it doesn't fail). Was there a security hole in sp_changedbowner, until SP3a?

Does that line about "RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles" mean each user must have both server roles, as opposed to either?|||i dont believe that you have to have dual membership
but it should be relevant to try. and see what pops up
i am more concerned about what has changed between sp2 and sp3 to cause this to happen.

Our DBAs initially set up databases for each developer, so they should be appropriately configured at that point. Each developer login has the dbcreator role. Each developer's login owns the corresponding database.
except for dbcreator what are their admin privileges?
do they own the dbs that they work on?|||I might be wrong about this, but it sounds like you're developers are restoring on other servers, perhaps their own pcs? Anyway, you probably just have a SID issue here. What you need to do is have them drop their permissions, then run sp_help_revlogin to sync up the SIDs on the user accounts. That should solve the restore and the sp_changedbowner problem.

Take these two scripts and put them in your master database on the source server.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_hexadecimal Script Date: 5/17/2004 9:46:25 PM ******/
ALTER PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.sp_help_revlogin Script Date: 5/17/2004 9:46:39 PM ******/
ALTER PROCEDURE sp_help_revlogin @.login_name
sysname = NULL
AS
--Declare needed variables.
DECLARE
@.name sysname,
@.xstatus int,
@.binpwd varbinary (256),
@.txtpwd sysname,
@.tmpstr varchar (256),
@.SID_varbinary varbinary(85),
@.SID_string varchar(256),
@.dbname varchar(255)
--Determine whether to process one login or all. Set up cursor accordingly.
IF (@.login_name IS NULL)
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> 'sa'
END
ELSE
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> @.login_name
END
OPEN login_curs
FETCH NEXT FROM login_curs
INTO
@.SID_varbinary,
@.name,
@.xstatus,
@.binpwd,
@.dbname
--If no logins found, exit the procedure.
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SELECT @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SELECT @.tmpstr =
'** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE
BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication

IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password

EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
BEGIN
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
END
ELSE
BEGIN
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
END

PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr =
'EXEC master..sp_addlogin ''' + @.name + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE
BEGIN
BEGIN

-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr =
'EXEC master..sp_addlogin ''' + @.name + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
END
IF (@.xstatus & 2048) = 2048
BEGIN
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
PRINT @.tmpstr
END
ELSE
BEGIN
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
--Add the default database.
SET @.tmpstr = 'EXEC master..sp_defaultdb ''' + @.name + ''',''' + @.dbname + ''''
PRINT @.tmpstr
END
FETCH NEXT FROM login_curs
INTO
@.SID_varbinary,
@.name,
@.xstatus,
@.binpwd,
@.dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Run the sp_help_revlogin from the master database. Copy the results of the user they need, and have them run the script on their personal server.s They should then be able to do a restore.|||Thanks for the procedures -- I think we'll make good use of them.

I'm working with two servers at the moment -- A is Win2k-sp3/SQL2k-RTM, while B (the problem child) is Win2k3-RTM+hotfixes/SQL2k-sp3a . The dumps are generally from A, and we're trying to migrate developers to B. However, both will be needed for the forseeable future, as both platform combinations are supported. So, one of them will need to take dumps of the other.

To avoid constantly maintaining this, would it be sufficient to simply create a 'dump master' account on each machine? This account would have the same SID on each machine. We can also try to keep each developer's SID consistent across boxes (if/when they use multiple servers).

I have another server box C, which is Win2k-sp4/SQL2k-sp3a+818 . It also chokes on this dump from server A, and a dump from server B. One odd part is that I can't even restore a dump created from one login's database on server B to a different login's database on server B, without getting these permissions issues.

We've fortunately kept the budget supplying us with enough hardware for good, central database servers. Meanwhile, the DBAs have put enough Fear Of God in the developers (and kept enough control of the install CDs) that developers don't have local database servers. This could be worse, and isn't.|||Other than dbcreator role, they are standard logins. They each own their own database, so within that db they're dbo.|||I would try the single 'dump master' account. This is a lot of authority to give someone though. Have you thought about just setting up a directory and table structure to support this. You can put new dumps into a directory and make a table entry. You have a job that sweeps through every 15 minutes or so and does restores.

My biggest concern with the whole operation would be the fact you don't have SP3a across the board. You are running a server RTM?? That's insane.|||I'm as nervous as you are about that RTM server... but someone didn't write license or support contracts that exclude out of date service levels, so we have to keep an RTM box around. (It's a QA regressions box.)|||It's a freaking free service pack update. Tell your clients to use their brains. That one server can endanger your whole enterprise, unless it's just a standalone box sitting on a shelf with a keyboard and monitor attached to it.|||Many of our customers are ... cautious... about service packs. They're too large to want to apply them. Either the bureaucracy is too thick for those who must approve the work to understand the value. Or, they want uptime on the app (sales guys hate not seeing their commission amounts) and don't want to endanger or delay a payroll source because of an unknown service pack.

Others have been hit with performance issues after applying service packs. (SQL Server 2000, SP1 to SP2)

They also have a habit of going against our wishes by deploying other apps against the same SQL Server instance. This then means they have two (or more!) apps to re-validate after the service pack! More than once the other app vendor "hasn't certified" the latest service pack, so customer (believes it) is stuck.

Besides, if they actually had brains which they could use, they would have deployed our $VERY_LARGE app against Oracle (or even DB2) :^) Sales people seem to believe "one sale is as good as another" ... ughh|||The fun part of the automated restore daemon approach is groking the structure of the dump -- file groups, files, etc. Once the files are known, appropriate MOVE options can be crafted, and a restore can start. (See new thread...)

Logins and sp permissions

I am using NT logins and have Users defined in Security section of
Enterprise Manager and also in the database/users section, as db_Owners.
I can only get users to access the .ade file successfully from certain PCs.
The error is related to permissions on particular stored procedures that are
used to poulate the inital screen. The stored procedures populate list boxes
and appear blank when opened. However I have buttons associated with the
list loxes that sort the data via each column in the list box, and when
these are used the data appears in the list boxes.
On the PCs that fail the stored procedures are suffixed by ;1
I feel that it maybe PC specific but the workstations involved are similar
using a standard desktop configuration, latest service packs etc
TIA
WarwayTry using SQL Profiler to capture the traffic between a working machine and
a client that is failing.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Logins and Permissions

I have always worked out of Enterprise Manager to view the permissions/users,
etc. in a given database (SS 2000). I often script adding permissions and
user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
to see all users in a database and their database level permissions
(db_reader, etc.) as well as their specific table-level and proc-level
permissions. What system stored procs are there to do that? I've got a lot
of databases and I've found Ent Mgr is just too slow.
Nevermind. I answered my own q.
"CLM" wrote:

> I have always worked out of Enterprise Manager to view the permissions/users,
> etc. in a given database (SS 2000). I often script adding permissions and
> user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
> to see all users in a database and their database level permissions
> (db_reader, etc.) as well as their specific table-level and proc-level
> permissions. What system stored procs are there to do that? I've got a lot
> of databases and I've found Ent Mgr is just too slow.

Logins and Permissions

I have always worked out of Enterprise Manager to view the permissions/users,
etc. in a given database (SS 2000). I often script adding permissions and
user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
to see all users in a database and their database level permissions
(db_reader, etc.) as well as their specific table-level and proc-level
permissions. What system stored procs are there to do that? I've got a lot
of databases and I've found Ent Mgr is just too slow.Nevermind. I answered my own q.
"CLM" wrote:
> I have always worked out of Enterprise Manager to view the permissions/users,
> etc. in a given database (SS 2000). I often script adding permissions and
> user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
> to see all users in a database and their database level permissions
> (db_reader, etc.) as well as their specific table-level and proc-level
> permissions. What system stored procs are there to do that? I've got a lot
> of databases and I've found Ent Mgr is just too slow.

Logins and Permissions

I have always worked out of Enterprise Manager to view the permissions/users
,
etc. in a given database (SS 2000). I often script adding permissions and
user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I want
to see all users in a database and their database level permissions
(db_reader, etc.) as well as their specific table-level and proc-level
permissions. What system stored procs are there to do that? I've got a lot
of databases and I've found Ent Mgr is just too slow.Nevermind. I answered my own q.
"CLM" wrote:

> I have always worked out of Enterprise Manager to view the permissions/use
rs,
> etc. in a given database (SS 2000). I often script adding permissions and
> user with sp_addrolememeber and sp_grantdbaccess, etc. But let's say I wa
nt
> to see all users in a database and their database level permissions
> (db_reader, etc.) as well as their specific table-level and proc-level
> permissions. What system stored procs are there to do that? I've got a l
ot
> of databases and I've found Ent Mgr is just too slow.

Login Permissions.....

I have a login user Id which is listed in Security->Login node.
How to list the permissions this user Id has?
I know I can get this info from SP_HELPROTECT. Having trouble in writing the
exact syntax to execute the same. What is the exact syntax to list the
permissions.
SQL 2K.
Thank you,
AllenHi,
sp_helplogins <loginname>
This gives you the server roles assigned to that login as well as rights to
variros databases
To look into the object level prev. for a particular database.
EXEC sp_helprotect NULL, 'User_name'
Thanks
Hari
SQL Server MVP
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:O$k%232iNqFHA.616@.TK2MSFTNGP15.phx.gbl...
>I have a login user Id which is listed in Security->Login node.
> How to list the permissions this user Id has?
> I know I can get this info from SP_HELPROTECT. Having trouble in writing
> the
> exact syntax to execute the same. What is the exact syntax to list the
> permissions.
> SQL 2K.
> Thank you,
> Allen
>
>

Login Permissions.....

I have a login user Id which is listed in Security->Login node.
How to list the permissions this user Id has?
I know I can get this info from SP_HELPROTECT. Having trouble in writing the
exact syntax to execute the same. What is the exact syntax to list the
permissions.
SQL 2K.
Thank you,
Allen
Hi,
sp_helplogins <loginname>
This gives you the server roles assigned to that login as well as rights to
variros databases
To look into the object level prev. for a particular database.
EXEC sp_helprotect NULL, 'User_name'
Thanks
Hari
SQL Server MVP
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:O$k%232iNqFHA.616@.TK2MSFTNGP15.phx.gbl...
>I have a login user Id which is listed in Security->Login node.
> How to list the permissions this user Id has?
> I know I can get this info from SP_HELPROTECT. Having trouble in writing
> the
> exact syntax to execute the same. What is the exact syntax to list the
> permissions.
> SQL 2K.
> Thank you,
> Allen
>
>

Login Permissions.....

I have a login user Id which is listed in Security->Login node.
How to list the permissions this user Id has?
I know I can get this info from SP_HELPROTECT. Having trouble in writing the
exact syntax to execute the same. What is the exact syntax to list the
permissions.
SQL 2K.
Thank you,
AllenHi,
sp_helplogins <loginname>
This gives you the server roles assigned to that login as well as rights to
variros databases
To look into the object level prev. for a particular database.
EXEC sp_helprotect NULL, 'User_name'
Thanks
Hari
SQL Server MVP
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:O$k%232iNqFHA.616@.TK2MSFTNGP15.phx.gbl...
>I have a login user Id which is listed in Security->Login node.
> How to list the permissions this user Id has?
> I know I can get this info from SP_HELPROTECT. Having trouble in writing
> the
> exact syntax to execute the same. What is the exact syntax to list the
> permissions.
> SQL 2K.
> Thank you,
> Allen
>
>

login permissions

I created a new user and wanted them to only be able to access one database.
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for that
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
Dan D.
You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.
|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb and
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:

> You can't. They have access to these databases because they contain a guest
> user. This cannot be removed from master or tempdb however it can be removed
> from msdb. Note that if you do remove it from msdb then only sysadmins will
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>
>
|||> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages.
Sysadmins will.

> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.

> What happens if I remove guest from master?
You can't do that.

> I don't understand why microsoft would allow anyone to be able to query the
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...[vbcol=seagreen]
> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages. If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query the
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change any
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:

> Sysadmins will.
>
> Yes. But again, no need to add sysadmins.
>
> You can't do that.
>
> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>
>
|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.

> We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permissions on each object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...[vbcol=seagreen]
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.

> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is[vbcol=seagreen]
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
one[vbcol=seagreen]
msdb and[vbcol=seagreen]
the[vbcol=seagreen]
of) the registry.[vbcol=seagreen]
one[vbcol=seagreen]
user[vbcol=seagreen]
msdb and[vbcol=seagreen]
master?[vbcol=seagreen]
the[vbcol=seagreen]
guest[vbcol=seagreen]
removed[vbcol=seagreen]
will[vbcol=seagreen]
that one[vbcol=seagreen]
for[vbcol=seagreen]
can I[vbcol=seagreen]
database.[vbcol=seagreen]

login permissions

I created a new user and wanted them to only be able to access one database.
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for tha
t
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
--
Dan D.You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb an
d
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:

> You can't. They have access to these databases because they contain a gues
t
> user. This cannot be removed from master or tempdb however it can be remov
ed
> from msdb. Note that if you do remove it from msdb then only sysadmins wil
l
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>
>|||> I'm not sure that I understand. If I remove the guest user from msdb no one">
> will be able to create/manage job and save dts packages.
Sysadmins will.

> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb
and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.

> What happens if I remove guest from master?
You can't do that.

> I don't understand why microsoft would allow anyone to be able to query th
e
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of
) the registry.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...[vbcol=seagreen]
> I'm not sure that I understand. If I remove the guest user from msdb no on
e
> will be able to create/manage job and save dts packages. If I add each use
r
> who I want to be able to create/manage jobs and save dts packages to msdb
and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query th
e
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
>|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change an
y
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:

> Sysadmins will.
>
> Yes. But again, no need to add sysadmins.
>
> You can't do that.
>
> Compare it to Windows registry. A Windows user need permissions to (parts
of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>
>|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.

> We're trying to
> set up a login for a client. We created a database for them and we want th
em
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permis
sions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...[vbcol=seagreen]
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want th
em
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.

> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is[vbcol=seagreen]
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>
one[vbcol=seagreen]
msdb and[vbcol=seagreen]
the[vbcol=seagreen]
of) the registry.[vbcol=seagreen]
one[vbcol=seagreen]
user[vbcol=seagreen]
msdb and[vbcol=seagreen]
master?[vbcol=seagreen]
the[vbcol=seagreen]
guest[vbcol=seagreen]
removed[vbcol=seagreen]
will[vbcol=seagreen]
that one[vbcol=seagreen]
for[vbcol=seagreen]
can I[vbcol=seagreen]
database.[vbcol=seagreen]

login permissions

I created a new user and wanted them to only be able to access one database.
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for that
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
--
Dan D.You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb and
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:
> You can't. They have access to these databases because they contain a guest
> user. This cannot be removed from master or tempdb however it can be removed
> from msdb. Note that if you do remove it from msdb then only sysadmins will
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >I created a new user and wanted them to only be able to access one
> >database.
> > For that one database on the "database access" tab I checked only that one
> > database and checked the "public" and "db_datareader" database roles for
> > that
> > one database.
> >
> > When I log in through QA they can also see the "master", "msdb" and
> > "tempdb"
> > databases. And they run select queries on the "master" database. How can I
> > prevent this? I only want them to be able to "see" the "stone" database.
> >
> > Thanks,
> > --
> > Dan D.
>
>|||> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages.
Sysadmins will.
> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.
> What happens if I remove guest from master?
You can't do that.
> I don't understand why microsoft would allow anyone to be able to query the
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages. If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query the
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
>> You can't. They have access to these databases because they contain a guest
>> user. This cannot be removed from master or tempdb however it can be removed
>> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> be able to create/manage jobs and save DTS packages to the server.
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >I created a new user and wanted them to only be able to access one
>> >database.
>> > For that one database on the "database access" tab I checked only that one
>> > database and checked the "public" and "db_datareader" database roles for
>> > that
>> > one database.
>> >
>> > When I log in through QA they can also see the "master", "msdb" and
>> > "tempdb"
>> > databases. And they run select queries on the "master" database. How can I
>> > prevent this? I only want them to be able to "see" the "stone" database.
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change any
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages.
> Sysadmins will.
>
> > If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok?
> Yes. But again, no need to add sysadmins.
>
> > What happens if I remove guest from master?
> You can't do that.
>
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases.
> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages. If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok? What happens if I remove guest from master?
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases. Is there a reason?
> >
> > Thanks,
> >
> > "Jasper Smith" wrote:
> >
> >> You can't. They have access to these databases because they contain a guest
> >> user. This cannot be removed from master or tempdb however it can be removed
> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
> >> be able to create/manage jobs and save DTS packages to the server.
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >> >I created a new user and wanted them to only be able to access one
> >> >database.
> >> > For that one database on the "database access" tab I checked only that one
> >> > database and checked the "public" and "db_datareader" database roles for
> >> > that
> >> > one database.
> >> >
> >> > When I log in through QA they can also see the "master", "msdb" and
> >> > "tempdb"
> >> > databases. And they run select queries on the "master" database. How can I
> >> > prevent this? I only want them to be able to "see" the "stone" database.
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>
> >>
>
>|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages. If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok? What happens if I remove guest from master?
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a guest
>> >> user. This cannot be removed from master or tempdb however it can be removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only that one
>> >> > database and checked the "public" and "db_datareader" database roles for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How can I
>> >> > prevent this? I only want them to be able to "see" the "stone" database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts
of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages. If I add each
user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok? What happens if I remove guest from
master?
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a
guest
>> >> user. This cannot be removed from master or tempdb however it can be
removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins
will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only
that one
>> >> > database and checked the "public" and "db_datareader" database roles
for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How
can I
>> >> > prevent this? I only want them to be able to "see" the "stone"
database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>