Restrict user from default tables (access given by public role)

I am newbie.. probably this is a silly question..
I added few users with only SELECT permissions to 2 User Tables. But
when these users login,they could also see multiple system tables
(syscontraints, syssegments , dtproperties) . From reading this forum
and other articles, I understand these are visible due to the user
being part of the default "public" role.
My Question is, is there any way to remove/hide these system tables
from the user?.. The team manager doesnt like these users seeing
anything other than the 2 User Tables they supposed to see..
Thanks in advance for all tips/advices/suggestionsTell your team manager he's a turd. No...actually don't say that, it'll
probably get you fired.
I wouldn't go messing around with the permissions on the system tables,
you're likely to stuff up some functionality of SQL Server in that DB
for those users. The system tables are a very necessary part of
day-to-day data & schema manipulation...for every user of the DB.
Things like optimising query plans, for example, rely on data stored in
the system tables. I'm not positive if things will start breaking if
you start revoking or denying permissions on those system tables but I
wouldn't risk it.
There's an option in SSMS (Tools | Options | Environment | General |
Hide system objects in Object Explorer), not sure about SQLEM or QA,
that will hide the system tables in the GUI but not change their
underlying permissions. That may placate the pointy-haired manager
<http://www.dilbert.com/> asking for this change, perhaps.
What tool are the users using to "see" these system tables?
*mike hodgson*
http://sqlnerd.blogspot.com
sreejith.ram@.gmail.com wrote:

>I am newbie.. probably this is a silly question..
>I added few users with only SELECT permissions to 2 User Tables. But
>when these users login,they could also see multiple system tables
>(syscontraints, syssegments , dtproperties) . From reading this forum
>and other articles, I understand these are visible due to the user
>being part of the default "public" role.
>My Question is, is there any way to remove/hide these system tables
>from the user?.. The team manager doesnt like these users seeing
>anything other than the 2 User Tables they supposed to see..
>Thanks in advance for all tips/advices/suggestions
>
>|||Thanks Mike... This convinced me I should spend my time convincing the
manager to leave it alone than trying to hide the tables
The users are using SQL Server Enterprise Manager.|||> The users are using SQL Server Enterprise Manager.
EM is more of a DBA/Developer tool than an end-user tool. In any case, you
can hide most of the system object noise by unchecking the 'show system
databases and system objects' option under the EM server registration
properties.
Hope this helps.
Dan Guzman
SQL Server MVP
<sreejith.ram@.gmail.com> wrote in message
news:1138983030.862156.116660@.f14g2000cwb.googlegroups.com...
> Thanks Mike... This convinced me I should spend my time convincing the
> manager to leave it alone than trying to hide the tables
> The users are using SQL Server Enterprise Manager.
>|||I thought there was that "hide" option in SQLEM. I just couldn't
remember where it was (been using SSMS too long <g> ).
*mike hodgson*
http://sqlnerd.blogspot.com
Dan Guzman wrote:

>EM is more of a DBA/Developer tool than an end-user tool. In any case, you
>can hide most of the system object noise by unchecking the 'show system
>databases and system objects' option under the EM server registration
>properties.
>
>

Restrict Port Security

I'm going to be installing a sql server behind a firewall. I'd appreciate
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?
Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:

> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also wanted
> to know if there was a way to secure a sql server by granting access only to
> specific IP addresses?
>
>
|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...[vbcol=seagreen]
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:

Restrict Port Security

I'm going to be installing a sql server behind a firewall. I'd appreciate
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:

> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also want
ed
> to know if there was a way to secure a sql server by granting access only
to
> specific IP addresses?
>
>|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...[vbcol=seagreen]
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
>

Restrict Port Security

I'm going to be installing a sql server behind a firewall. I'd appreciate
any best practice advice on changing the default port number and also wanted
to know if there was a way to secure a sql server by granting access only to
specific IP addresses?Hi Scott,
Really best practice is changing the default port number of the SQL Server.
But granting access to specific IP addresses only machine 1 to machine 2 or
ip address.
Hugs,
Rodrigo Fernandes
MCSE/MCDBA
"scott" wrote:
> I'm going to be installing a sql server behind a firewall. I'd appreciate
> any best practice advice on changing the default port number and also wanted
> to know if there was a way to secure a sql server by granting access only to
> specific IP addresses?
>
>|||What do you mean by saying 'but granting access ...'?
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in
message news:94A130F7-B240-44DF-9054-9E2BC08D9DE3@.microsoft.com...
> Hi Scott,
> Really best practice is changing the default port number of the SQL
> Server.
> But granting access to specific IP addresses only machine 1 to machine 2
> or
> ip address.
> Hugs,
> Rodrigo Fernandes
> MCSE/MCDBA
> "scott" wrote:
>> I'm going to be installing a sql server behind a firewall. I'd appreciate
>> any best practice advice on changing the default port number and also
>> wanted
>> to know if there was a way to secure a sql server by granting access only
>> to
>> specific IP addresses?
>>sql

Restoring to new database doesn't default DB file names

In SQL 2000 EM, when you restore a database from an existing database backup but specify a new database name to restore to, the filenames are automatically updated to match the new name of the database that will be restored.

I tried to do this with SQL 2005 Management Studio but it appears that the filenames aren't updated i.e. they are the same as the original database filenames. Do i have to manually go and change the filenames to reflect the fact that I'm creating a new database via a restore? If so, why doesn't the behaiour follow that of SQL 2000?

Thanks

Hello there

This is by design in SQL Server 2005.

There are two ways to restore to a new database:
1- By launching the restore database dialog from the context of an existing database. This would pre-load the existing backup set. You could change the database name & the dialog would automatically change the DB filenames for you.

2- By launching the resotore database from the 'Databases' node & later specifying the backup set by selecting a backup file thru 'device' option.

#1 above should work fine when you change the database name & hit 'Ok'

#2 above doesn't automatically change the DB file names for you. This is by design in SQL Server 2005. In SQL Server 2000 too, you had to go to the 'options' page for the dialog to automatically change file names.

The grid on the options page in SQL Server 2005's restore dialog is editable. You could directly edit the file names in the grid to accomplish the action.

Thanks,

Restoring the Northwind and Pubs databases

Is there an easy way to get Northwind and Pubs back to their default state,
assuming that you were too stupid to back them up before you started mucking
around with them? Thanks.You can recreate the two databases from the two scripts: instpubs.sql, and
instnwnd.sql from the \install folder of your SQL Server installation or
the SQL Server CD.
Sincerely,
Yih-Yoon Lee
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Alternatively, you can try attach Nothwind and pubs databases from the SQL
2K CD:
x86\DATA\northwnd.mdf
x86\DATA\northwnd.ldf
x86\DATA\pubs.mdf
x86\DATA\pubs_log.ldf
Richard
"Garrett Fitzgerald" <gfitzger@.nyx.net> wrote in message
news:uO$K4EbQDHA.2128@.TK2MSFTNGP12.phx.gbl...
> Is there an easy way to get Northwind and Pubs back to their default
state,
> assuming that you were too stupid to back them up before you started
mucking
> around with them? Thanks.
>
>|||Alan,
>> You also have to run a batch utility...
Thanks Alan. I wasnt aware of that.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alan Brewer [MSFT]" <alanbr@.microsoft.com> wrote in message
news:uwBiD8kQDHA.1564@.TK2MSFTNGP12.phx.gbl...
> You also have to run a batch utility to fully populate the data in pubs.
> Northwind can be resinstalled by just using the instnwnd.sql script.
> The processes to create pubs and Northwind are covered in these
Transact-SQL
> Reference topics:
>
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_pubs_2v8l.asp?frame=true
>
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_north_2ch1.asp?frame=true
> --
> Alan Brewer [MSFT]
> Lead Technical Writer
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
>

restoring replicated database

Hi,
I've restored a merge replicated database (publisher) on a new server
without replication.
Now i observe that there are some default replication procedures named
*_pal in my restored database. What is the way of deleting these
replication procedures. What will be the impact of the same.
Regds,
amit
Amit,
these procedures will need cleaning up by hand. sp_removedbreplication will
remove many system objects but these procs sometimes remain. You can
hand-craft a script to delete them (using information_schema.routines) or
just do it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul thanks for you reply. We have configured this restored database
for replication and it has created new *pal. procedures. Is it ok if we
delete the earlier *pal procedure now?
|||Yes
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanks, but can you just let me know if there would be any impact if i
keep the old procedures instead of deleting them as i would not like to
change the system configuration if there is not impact.
|||If you mean problems caused by name conflicts and the like, I think the
complexity of the name ensures this isn't the case. I have seen systems that
have accumulated orphaned procs like this over several years and the DBAs
didn't even notice they were there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Restoring Databases from Win2000 to Win2003/Collation

Default Collation is as follows:-

SQL Server 2000 on Windows 2000: SQL_Latin1_General_CP1_CI_AS
SQL Server 2000 on Windows Server 2003: Latin1_General_CI_AS

Databases restored from the Windows 2000 Server to Windows Server 2003 have a different collation than what the Windows Server 2003s collation is.

Does anyone know if this new collation is a permanent change for Microsoft and Windows/SQL?

Has anybody figured out a way of changing the collation of a database without having to rebuild it into a new database or by rebuilding the master?

Any other work arounds?The attached stored procedure will generate a change script that will recollate a database. Check the script comments for known limitations. You can run this script from query analyser.

Essentially this generates a change script that: drops all indexes, constraints and statistics on columns that don't have the desired target collation. Alters the columns to the new collation, alters the database to the new collation, and replaces all the indexes, constraints and statistics removed in the first place.

I'm attaching the file in the message reply window. I hope it works.

HH