Public Server Role
Mar 30, 2006
In exploring permissions that users have, I find that they all have VIEW ANY DATABASE permission which they inherit from the public server role. You can see this by selecting the Permissions page on the Server Properties dialog and highlighting "public". The permission shows as having been granted by sa. This is listed as a server role. However, it does not show in the list of server roles and I can't find any documentation for it (RTM BOL). Interestingly, if I revoke this permission (which is the only permission this role has), the public server role disappears from view. But I can subsequently regrant the permission with Transact-SQL in master and the role comes back.
I would like more information about this role. It seems to be sort of "secret".
When I revoke the permission, users can't see any databases except master and tempdb (both of which have active guest users) even though they have been granted access to other databases.
What I was trying to accomplish by changing this permission was to allow a user to see only those databases which they are allowed to use. But that does not seem to be possible.
Thanks for the help.
Sharon
View 5 Replies
ADVERTISEMENT
Oct 17, 2007
Hello,
Does anybody have the transact SQL to find the permissions granted to the public role in a Database ?
Also looking for the SQL to find the permissions granted to the user Guest in a database ?
thanks
View 5 Replies
View Related
May 13, 2002
I am new at this and we encountered a problem. Can names in the public role be deleted? We have some names that need to go - however the delete option does not high light?
I would appreciate your help..
View 8 Replies
View Related
Oct 3, 2006
In a SQL 2k instance (latest SP) some of my user databases show the public role with execute on a variety of stored procs named dt_* (i.e. dt_addtosourcecontrol). However, not all the user databases do this, some do not grant the public role execute on these procs .
So, can someone explain what generates these permissions and is it acceptable to remove them? If I have a database that does not grant public access, should I be concerned? I don't see any reference to these procs in BOL.
TIA,
Moblex
View 1 Replies
View Related
Aug 24, 2007
Is it possible to script the Public role? Delete authorities granted to Public?
I need to copy all the permissions of the Public role from one database to many others. The databases were setup incorrectly with many authorities granted to Public.
View 1 Replies
View Related
May 24, 2000
Hello all,
I'm having trouble copying my production database to a development version because I have a login user in the public role that is not a valid user. I can't find any reference to the login/user anywhere in my database or in NT security, on my server for that matter.
When I open the public role through Enterprise Manager, I can see the logins/users in the list, but the 'Remove' button is disabled. I also tried to use the stored procedure 'sp_droprolemember', but it says that 'public' is not a role in the database.
DTS keeps blowing up on this object when exporting, and I need to get this stuff copied ASAP.
Thanks for all help
Kevin
View 1 Replies
View Related
Mar 27, 2008
Can someone tell me what permissions has public role in sql server? can it read every table or it can just connect to a database and no more?
thanks
View 1 Replies
View Related
Sep 19, 2005
I gave a developer rights to the Public role on a SQL Server 2000 database. The Public role only has explicitly set select rights to the system tables and one user table. There are no other explicit rights set. The developer was able to open a table that had no rights set in enterprise manager and change data. Is this possible?
View 1 Replies
View Related
Jul 20, 2007
I use the following script in order to create db role:
USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [public]GO
It doesn't work:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'public'.
However this code works fine:
USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [dbo]GOALTER AUTHORIZATION ON ROLE::[myRole] TO [public]GO
So the question is why?
View 6 Replies
View Related
Dec 22, 2000
Can anybody explain what database user with a prefix of "" in the public role indicates. I have the same two users in every database and cannot remove them?
Any help would be gratefully received.
View 4 Replies
View Related
Sep 13, 2004
Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.
What can I do so that users are able to select from these tables after they are created?
Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?
All help is appreciated.
View 2 Replies
View Related
Oct 14, 2004
We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated.
View 5 Replies
View Related
Sep 8, 2006
Hi all,
I have setup a new SQL 2000 SP4 and internal auditor query about revoke permission from Public role and remove guest from all databases.
1. Can I revoke all default permissions (select on system tables in all DBs) from "Public" role? I am concern any error after such action.
2. I found that guest account in DB -- master, tempdb and msdb. According to Microsoft documents. The account should not remove and can't from master and tempdb. How about msdb?
Thanks,
Regards,
Edwin
View 7 Replies
View Related
Oct 10, 2007
Hi:
When I restore DB from testing to production, we want to remove extra access rights granted to public group. Is there a simple way to query to find out for which objects (table, view, sp, fn) that public group were granted select, delete, update insert, or execute rights?
My objective is to write a sp to remove all user assigned rights to public group (role), but not to deny any rights. How to do it?
Any suggestion will be appreciated.
View 1 Replies
View Related
Feb 14, 2008
set up windows 2003 server
sql server 2000
have public IP from netword soluctions
I can see website
what settings to i need to open up sql server to the public?
View 12 Replies
View Related
May 1, 2006
I'm creating an asymmetric key in SQL Server:
CREATE ASYMMETRIC KEY MY_KEY
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'password1@'
I'm then taking the public key...
SELECT public_key
FROM sys.asymmetric_keys
WHERE [name] = 'MY_KEY'
I'm trying to import this into VB.Net using RSACryptoServiceProvider.
Dim RSA As New
Security.Cryptography.RSACryptoServiceProvider(512)
RSA.ImportCspBlob(KeyBlobByteArray)
I have to add the following line to get it to not give me errors.
(Why?) But the data looks right when I look at the exponet and modulus
of the parameters that are exported and then immediately imported.
Something is obviously being thrown away when I do this, but I don't
know what. It's probably the key to the whole problem.
RSA.ImportParameters(RSA.ExportParameters(False))
I encrypt the message...
EncryptedMessageByteArray = RSA.Encrypt(MessageByteArray,
False)
And in SQL server I try to decrypt the message and I get NULL.
SET @DecryptedMessage =
DecryptByAsymKey (
AsymKey_ID('MY_KEY') ,
@EncryptedMessage ,
N'password1@')
I've noticed that the encrypted array is completely different in VB
than when I encrypt it in SQL (obviously), but I can't figure out how
to configure to the RSACryptoServiceProvider to match what SQL Server
is doing under the hood.
Any ideas?
Thanks,
Jerrill
View 10 Replies
View Related
Feb 14, 2008
set up windows 2003 serversql server 2000have public IP from netword soluctionsI can see websitewhat settings to i need to open up sql server to the public?
View 1 Replies
View Related
Sep 21, 2005
I am working with a SQLServer installation where all public permissionshave been revoked from the system.I currently hold the securityadmin and sysadmin roles to perform mylogicall access control work (creating logins and adding users todatabases and changing group memberships.)There has been a question as to whether or not I need the sysadmin roleto do my job, so we tried an experiment in dropping the sysadmin rolefrom my id.With no public permissions, I see no user information on the server,which really limits my ability to do the job.Has anyone ever worked with a super-locked-down server without thesysadmin role for doing security admin work?What I'm looking for is hopefully SQL to perform the access privilegesneeded for the security admin role by itself.Thanks in advance!
View 2 Replies
View Related
Apr 19, 2006
SQL Server Service 2005 Pack 1 is available here.
The list of new features and fixed was published here on KB Article 916940 .
Keep in mind to read the readme file first in order to avoid problems during the update or unexpected bahaviour.
Have Fun !
-Jens Suessmeyer.
View 6 Replies
View Related
Feb 21, 2007
I need a master view that gets data from different DBs located on the same SQL Server (similar with Oracle feature that allows to get data from different schemas).
Is it possible to create such a view ?
Thanks
View 5 Replies
View Related
Jun 3, 2015
We are planning to encrypt few fields using asymmetric encryption. Tyring share public key with users and retain private key with us. How to generate keys? Haven't found any solid document on how to generate these keys.
View 0 Replies
View Related
May 18, 2007
Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
thanks
View 2 Replies
View Related
Jan 9, 2006
In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
View 1 Replies
View Related
Jan 10, 2003
Hi!
What server role should be assign for individual to create and execute DTS packages.
Thank you,
Elena.
View 3 Replies
View Related
Mar 24, 2008
what views table that I can use to view the server roles on the instance?
thanks,
View 1 Replies
View Related
Jan 5, 2005
Where can I find the information about dbcc setcpuweight? I checked books online and couldn't find it. I also went and searched Google with the same results. Any help is greatly appreciated.
View 2 Replies
View Related
Jun 23, 2005
Hi:
I have a couple enter prise SQL 2000 servers, each might contain around 800 customer databases. each of those 800 databases has its own sql standard login and password and corresponding permission on table select/update/delete and proc exec rights.
I want to create a sever level single login and password, which could read-only those 800 databases all tables only data. Those 800 databases are not always the same, some might be dropped out, new might be added.
If this is impossible, what might be the alternative?
thanks
-D :)
View 2 Replies
View Related
Feb 14, 2008
I have a user with DBCREATOR Server role only. That user is able to create database but create table permission denied.
how would I set permission on this user, so that this user can create databases and automatically becomes the DBOWNER of that database and can do any action on that database.
Thanks,
View 6 Replies
View Related
Jan 23, 2008
Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)
for the given connection string Dim connstring
As String = "Data Source=Northwind;Initial
Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"
Dim cmd
As SqlCommand
Dim
param As SqlParameter
Dim
cookie As Byte()
Dim cn As New
SqlConnection(connstring)
If
(cn.State = ConnectionState.Closed) Then
cn.Open()
End If Please help..
Thanking you, Nirmala
View 2 Replies
View Related
Apr 20, 2005
A problem: I created some user defined roles in SQL SERVER 2000 and assigned users to different roles. Can you help me with a way (stored procedure etc.) to return the correct roles of my logged in users to my ASP.NET application.
View 1 Replies
View Related
Jul 23, 2005
I would like to determine if a particular user has sysadmin serverrole. Is there a way to do this via the connection string? Currentlyour code checks if a login is valid using SQLDriverConnect, however weneed to be certain that the user can login and modify the schema.Is it possible to fetch a user's server role to determine if it has asysadmin server role?
View 2 Replies
View Related
Aug 29, 2005
I wish to create a user that can backup any or all databases in our SQLServer 2000 Instance. I thought there would be a server role for thisfunction, however I can only find that after I grant access of adatabase to the user, then I can choose ds_backupoperator.I want to create a user that will have the ability to backup all thedatabases. I dont wish to have to come back to the server after a newtable is created and add the backup user to that table.I want SA w/o the full privilage...am I crazy?Any Suggestions?TIARobBackgroup: We currently have about 10 SQL servers, and adding more inthe future. I am using SQLBackup from Idera along with HP SurestoreTape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) withArcServe from Computer Associates. I want to have this automated tobackup to file then tape, regardless of what databases get created.
View 1 Replies
View Related
May 11, 2008
Hi all,
Is it possible to reconfigure the sysadmin server role?
How can I see it's permissions?
Thanks,
Assaf
View 1 Replies
View Related