Re: PUBLIC Role - What am I missing?

From: John K. (cgi_777@hotmail.com)
Date: 10/09/02 

From: "John K." <cgi_777@hotmail.com>
Date: Tue, 8 Oct 2002 19:01:23 -0700

Hi Bill,

I don't know the easy way to change ownership of a role. But it looks like
you can make it less painful by using the EM and "Generating SQL Script"
under All Task for the database and generate all the permissions for that
role.

Check the script all objects on the general panel
Check ONLY the " .. Create <object> .. " on the format panel
Check "Script database users and database roles" on the options panel

After generating the SQL script file then extract all the grants and denies
for that role.
Then in QA drop the role and recreate the new role with the new owner and
last but not least, run the extracted grants and denies from the generated
script. It's easier than doing it by hand.

You might have to play around with the different checkboxes in the different
panels to get every grant/deny permission to be generated other than what I
have mentioned above. But what I did worked pretty good and pretty fast.

Hope that is of some help.
John

PS

Someone should write a sproc that does this functionality of changing the
owner of a Role.. Wouldn't that be great!

"Bill Carlson" <bill_carlson2@yahoo.com> wrote in message
news:e7c867c3.0210051752.573c7041@posting.google.com...
> Hello all. I'm new to SQL security and wondering if the Public role
> is as big of a pain as it appears. Our goal is to allow the minimum
> amount of access neccesary to read a single view in Access/EM and call
> a single stored procedure. It appears we can't do this without also
> allowing:
>
> - Users to be able to enumerate databases (master.sysdatabases)
> - Look at SPROC text (syscomments)
> - Determine physical database structure (sysobjects)
> - Find out "stuff" about the system from master (sp_help*)
>
> What am I missing? I can get rid of Public's permissions one by one,
> but this is tedious and there's the danger of breaking external tools.
> Are there scripts available that close down Public as much as
> feasable? Will these scripts break when a new version of SQL comes
> out?
>
> Thanks in advance for any help that can be provided. Tell me it isn't
> this bad!

Relevant Pages

  • Re: How can I make a primary key in my SQL database?
    ... > I normally use code like this when connecting to my database... ... How can I define a Primary Key? ... script is to use the Generate SQL Script function in Enterprise Manager. ...
    (microsoft.public.inetserver.asp.db)
  • Re: How to generate scripts from existing triggers?
    ... Open Enterprise manager -- Select database and right click and select all ... Select "Generate SQL Script ... In options -- check the "SCRIPT TRIGGER" ... triggers to a specific location ...
    (microsoft.public.sqlserver.programming)
  • How to create the database?
    ... I've been tinkering around with SQL script and I'm getting the ... to download and create the database on my handheld. ... over a 1 MB flat data file to load up into the database and then, ...
    (microsoft.public.sqlserver.ce)
  • EM SQL Script generation BUG?
    ... I'm using Enterprise Manager to generate a SQL script from a database, ... This appears to be a bug in the script generator. ...
    (microsoft.public.sqlserver.tools)
  • Re: Get custom database properties from an Access database
    ... It doesn't matter if the VBScript process drags, since it is an independent process and so will have no effect on the user's interaction with the database. ... The user can skip merrily along, doing whatever he needs while the script invisibly figures out the current situation in the background and decides what to do about it. ... It does, but I've run into issues other places on this same network, where users needed a drive letter to a server. ...
    (microsoft.public.scripting.vbscript)