Re: Windows Power User SQL

Is there any way to prevent users accessing the master
database through the guest account.

The guest user must have connect permission in master and tempdb. However, the default guest user permissions are minimal.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Sammy" <Sammy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:4772FCA7-5D30-4F81-B7E4-1254C5E491CC@xxxxxxxxxxxxxxxx
Hi Uri or Erland

When I run from the master database for example testing against user bill

execute as user= 'domain\bill.dobbs'

SELECT SUSER_NAME(), USER_NAME();
results = domain\bill.dobbs' , guest

Bill has not been given any access to the master database, but as guest is
enabled there it seems anyone can run queries on the master database through
the account guest. I tried revoking access to the guest account to master
without any success. Is there any way to prevent users accessing the master
database through the guest account.

thanks
for any help

Sammy

"Uri Dimant" wrote:

Sammy
SELECT HAS_DBACCESS ( 'database_name' )


In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).

> For example, I would like to list all stored procedures which a role > has
> execute permission for.

This is an example of usage:

SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o


"Sammy" <Sammy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:538F09E5-C03D-4E8B-BBA4-DAC3F29DD6D0@xxxxxxxxxxxxxxxx
> Hi,
>
> On one of our Sql 2005 servers someone added a user to a server as a
> windows
> power user with permission to log onto the server and have given them
> permission to just read and write on one database.
>
> They have been able to backup a database and view the list of tables on > a
> linked server.
>
> How can I find out the whole list of permissions the user has on the > sql
> server as checking the actual permissions they only have read and write
> permissions on one database and are not a member of any server roles.
>
> Very confused any suggestions please let me know
>
> Sammy




.

Relevant Pages

  • Re: T-SQL Debugger Error
    ... denied on object 'sp_sdidebug', database 'master', owner 'dbo'. ... and when I grant the permission of sp_sdidebug, ... I am using following statement to grant the permission. ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Default Login?
    ... The guest database user provides logins with a security context when ... they haven't been explicitly granted database access. ... You can ignore the default login reported by xp_loginconfig. ... > Master Database. ...
    (microsoft.public.sqlserver.security)
  • Re: Guest account in SQL 2005?
    ... The guest account in SQL Server serves the same basic function as the ... just like you wouldn't grant permissions to the Everyone group in Windows. ... priviledges in the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Is there a way to Use an Application Role with more than one database?
    ... So you are still getting the 'is not a valid user in database' error? ... Did you enable the guest user? ... permissions to systems objects in the ProductionDB?) ... EXEC sp_dboption 'DevelopmentDB', 'db chaining', true ...
    (microsoft.public.sqlserver.security)
  • Re: code access security
    ... Error 1 CREATE ASSEMBLY for assembly 'GmsSqlClr' failed because assembly ... owner has EXTERNAL ACCESS ASSEMBLY permission and the database has the ... make sure the database owner is mapped to the correct login on ...
    (microsoft.public.dotnet.languages.csharp)