Hi,
I have a report with reference on ReportLibrary assembly.
In the Code section of the report there is the following code:
Public m_myClass As ReportLibrary.DatabaseInteraction
Protected Overrides Sub OnInit()
m_myClass = ReportLibrary.DatabaseInteraction.GetInstance()
End Sub
When I try to publish this report via Reporting Services API:
warnings = RS.CreateReport(reportName, reportPath, true, definition, null);
I get the following error:
+System.SystemException {"There is an error on line 0 of custom code:
[BC30002] Type 'ReportLibrary.DatabaseInteraction' is not defined. --> There
is an error on line 0 of custom code: [BC30002] Type
'ReportLibrary.DatabaseInteraction' is not defined."}
How I can solve this problem?
Any help would be appreciated.some addition:
my report was deployed from VS before and worked successfully.
this error is happened when I made some changes in the report and tried to
re-publish it.
"Paul Zorin" wrote:
> Hi,
> I have a report with reference on ReportLibrary assembly.
> In the Code section of the report there is the following code:
> Public m_myClass As ReportLibrary.DatabaseInteraction
> Protected Overrides Sub OnInit()
> m_myClass = ReportLibrary.DatabaseInteraction.GetInstance()
> End Sub
> When I try to publish this report via Reporting Services API:
> warnings = RS.CreateReport(reportName, reportPath, true, definition, null);
> I get the following error:
> +System.SystemException {"There is an error on line 0 of custom code:
> [BC30002] Type 'ReportLibrary.DatabaseInteraction' is not defined. --> There
> is an error on line 0 of custom code: [BC30002] Type
> 'ReportLibrary.DatabaseInteraction' is not defined."}
> How I can solve this problem?
> Any help would be appreciated.
Showing posts with label public. Show all posts
Showing posts with label public. Show all posts
Friday, March 9, 2012
Saturday, February 25, 2012
Public user; tempdb permissions
We have financial application that uses SQL 2000 as the backend database. As part of the setup we were told that the "public" user account needed "full permissions" to the tempdb.
So, I right-click on the tempdb and go the the properties and then to the permissions and give "public" rights for all options.
Everytime the system is restarted after MS critical updates or any other reason the "public" user is reset back to the default settings and I have to go back in and reset the permissions for "public" in the tempdb.
Anyone know why this is happening? We have a production box and a development box and they both do the same thing.
Thanks for any insight.just curious, but did they give you any reason for having to do that?
I can't think of any reason that you'd have to. Have you tried just leaving it alone? If so, do the users get any kind of errors?
also curious as to the application that's requiring you to do something like that|||The application is Portia from Thomson Financial. It is a portfolio management app.
If the public user does not have rights to the tempdb then some reports that users run will return no data and other problems like funny application error messages.
They didn't give a reason why this is required, but I am guessing it is a cover for poor application design.|||your guess is correct. my hunch is they create permanent objects in tempdb (each user upon being added to a database is automatically enrolled into public group of that database. in properties window of tempdb they require permissions given to public to create all objects, not sure about other functions like backup, etc.)|||You can't backup a TempDb !! All users have permissions in a temp db and only they can access the temp table created with "#" Prefix. However , a Permanent temp table created by one user is owned by that user and behaves like any other table in a regular database . All those permissions and object would be gone after the SQL is restarted because tempdb , as the name says is a temporary database and is recreated each time SQl Server restarts|||a permanent object cannot be created in a database, including tempdb, unless a user is explicitly granted permissions to create that type of object or the user belongs to the database group/role or the server fixed role that has those permissions. in this discussion the vendor requires public to have permissions to create certain objects in tempdb. of course it gets recreated every time the service restarts! that's not the question! the question is how to make it so that granting permissions will not be the chore to do every time it happens. i'd go with a startup procedure that would grant the required permissions to public.|||I think that besides creating a startup procedure to grant the rights you need to call that company and tell them that thier developers need to take a look at what they are doing and add 'reboot server and test functionality again' to thier QA process. I sure hope that's an alpha or beta release of that software... what a freakin' nightmare.
why in the world wouldn't they just create the objects in the database they are working in? or store those objects in another database if they had some concern about the objects being there.
sorry to sound ranty... that kind of stuff just plain bothers me|||you're right on man, right on!|||Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?|||Originally posted by aashu
Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?
BOL:
The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.|||If that is the case , Why not put all those users and permissions in Model !!
So, I right-click on the tempdb and go the the properties and then to the permissions and give "public" rights for all options.
Everytime the system is restarted after MS critical updates or any other reason the "public" user is reset back to the default settings and I have to go back in and reset the permissions for "public" in the tempdb.
Anyone know why this is happening? We have a production box and a development box and they both do the same thing.
Thanks for any insight.just curious, but did they give you any reason for having to do that?
I can't think of any reason that you'd have to. Have you tried just leaving it alone? If so, do the users get any kind of errors?
also curious as to the application that's requiring you to do something like that|||The application is Portia from Thomson Financial. It is a portfolio management app.
If the public user does not have rights to the tempdb then some reports that users run will return no data and other problems like funny application error messages.
They didn't give a reason why this is required, but I am guessing it is a cover for poor application design.|||your guess is correct. my hunch is they create permanent objects in tempdb (each user upon being added to a database is automatically enrolled into public group of that database. in properties window of tempdb they require permissions given to public to create all objects, not sure about other functions like backup, etc.)|||You can't backup a TempDb !! All users have permissions in a temp db and only they can access the temp table created with "#" Prefix. However , a Permanent temp table created by one user is owned by that user and behaves like any other table in a regular database . All those permissions and object would be gone after the SQL is restarted because tempdb , as the name says is a temporary database and is recreated each time SQl Server restarts|||a permanent object cannot be created in a database, including tempdb, unless a user is explicitly granted permissions to create that type of object or the user belongs to the database group/role or the server fixed role that has those permissions. in this discussion the vendor requires public to have permissions to create certain objects in tempdb. of course it gets recreated every time the service restarts! that's not the question! the question is how to make it so that granting permissions will not be the chore to do every time it happens. i'd go with a startup procedure that would grant the required permissions to public.|||I think that besides creating a startup procedure to grant the rights you need to call that company and tell them that thier developers need to take a look at what they are doing and add 'reboot server and test functionality again' to thier QA process. I sure hope that's an alpha or beta release of that software... what a freakin' nightmare.
why in the world wouldn't they just create the objects in the database they are working in? or store those objects in another database if they had some concern about the objects being there.
sorry to sound ranty... that kind of stuff just plain bothers me|||you're right on man, right on!|||Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?|||Originally posted by aashu
Though I never tested it , but objects created in Model, would they be created in tempdb when system restarts ? OR Tempdb doesn't copy model structure ?
BOL:
The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.|||If that is the case , Why not put all those users and permissions in Model !!
Public Synonym?
Hi,
I am an Oracle Developer and just started working on a SQL Server database. I created a view and gave ALL rights to PUBLIC and my supervisor. However, when my supervisor logs on as herself she can see the query (in Query Analyzer) but she can't select data from it.
In Oracle I typically have to create a public synonym so users can use an object without specifying the owner of that object. Does the same apply in SQL Server and if so, how do I create a public synonym?
Thanks,
KDoes that login have access to the database - and within that database does public have "select" rights ? When you manage the permissions for the view do you see that login and if so click on "select" rights.|||Did you create your view in your own schema? (as in user.viewname)
Basically, MS goes through a routine for each table on each query. Suppose you issue:
select * from table
SQL Server first looks for username.table where username is your username. If that fails, SQL Server than looks for dbo.table. If that fails, then an error is returned. Because of that first lookup, it is suggested that you supply the owner of the table for queries that are run many many times per hour. The first lookup is not too work intensie, but if you do it a million times an hour...
As for a public synonym, you have to create a view as dbo.view. Then anyone can query it as just select * from view (provided they have no view or table of trhe same name in their own schema).
I am an Oracle Developer and just started working on a SQL Server database. I created a view and gave ALL rights to PUBLIC and my supervisor. However, when my supervisor logs on as herself she can see the query (in Query Analyzer) but she can't select data from it.
In Oracle I typically have to create a public synonym so users can use an object without specifying the owner of that object. Does the same apply in SQL Server and if so, how do I create a public synonym?
Thanks,
KDoes that login have access to the database - and within that database does public have "select" rights ? When you manage the permissions for the view do you see that login and if so click on "select" rights.|||Did you create your view in your own schema? (as in user.viewname)
Basically, MS goes through a routine for each table on each query. Suppose you issue:
select * from table
SQL Server first looks for username.table where username is your username. If that fails, SQL Server than looks for dbo.table. If that fails, then an error is returned. Because of that first lookup, it is suggested that you supply the owner of the table for queries that are run many many times per hour. The first lookup is not too work intensie, but if you do it a million times an hour...
As for a public synonym, you have to create a view as dbo.view. Then anyone can query it as just select * from view (provided they have no view or table of trhe same name in their own schema).
Public SS2005 newsgroups
Now that SS2005 is in public beta, are there any newsgroups available to the
public (or at least, those with the beta)?
Thanks,
Adam Machanic
online:
http://communities.microsoft.com/new...r2005&slcid=us
newsreader info is on that web page as well
Keith
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> Now that SS2005 is in public beta, are there any newsgroups available to
the
> public (or at least, those with the beta)?
>
> Thanks,
> Adam Machanic
>
|||Thanks!
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:usG5rTLdEHA.1040@.TK2MSFTNGP10.phx.gbl...
> online:
>
http://communities.microsoft.com/new...r2005&slcid=us
> newsreader info is on that web page as well
> --
> Keith
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> the
>
public (or at least, those with the beta)?
Thanks,
Adam Machanic
online:
http://communities.microsoft.com/new...r2005&slcid=us
newsreader info is on that web page as well
Keith
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> Now that SS2005 is in public beta, are there any newsgroups available to
the
> public (or at least, those with the beta)?
>
> Thanks,
> Adam Machanic
>
|||Thanks!
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:usG5rTLdEHA.1040@.TK2MSFTNGP10.phx.gbl...
> online:
>
http://communities.microsoft.com/new...r2005&slcid=us
> newsreader info is on that web page as well
> --
> Keith
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> the
>
Public SS2005 newsgroups
Now that SS2005 is in public beta, are there any newsgroups available to the
public (or at least, those with the beta)?
Thanks,
Adam Machaniconline:
" target="_blank">http://communities.microsoft.com/ne...lcid=us
newsreader info is on that web page as well
Keith
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> Now that SS2005 is in public beta, are there any newsgroups available to
the
> public (or at least, those with the beta)?
>
> Thanks,
> Adam Machanic
>|||Thanks!
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:usG5rTLdEHA.1040@.TK2MSFTNGP10.phx.gbl...
> online:
>
http://communities.microsoft.com/ne...er2005&slcid=usgreen">
> newsreader info is on that web page as well
> --
> Keith
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> the
>
public (or at least, those with the beta)?
Thanks,
Adam Machaniconline:
" target="_blank">http://communities.microsoft.com/ne...lcid=us
newsreader info is on that web page as well
Keith
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> Now that SS2005 is in public beta, are there any newsgroups available to
the
> public (or at least, those with the beta)?
>
> Thanks,
> Adam Machanic
>|||Thanks!
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:usG5rTLdEHA.1040@.TK2MSFTNGP10.phx.gbl...
> online:
>
http://communities.microsoft.com/ne...er2005&slcid=usgreen">
> newsreader info is on that web page as well
> --
> Keith
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> the
>
Public SS2005 newsgroups
Now that SS2005 is in public beta, are there any newsgroups available to the
public (or at least, those with the beta)?
Thanks,
Adam Machaniconline:
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us
newsreader info is on that web page as well
--
Keith
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> Now that SS2005 is in public beta, are there any newsgroups available to
the
> public (or at least, those with the beta)?
>
> Thanks,
> Adam Machanic
>|||Thanks!
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:usG5rTLdEHA.1040@.TK2MSFTNGP10.phx.gbl...
> online:
>
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us
> newsreader info is on that web page as well
> --
> Keith
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> > Now that SS2005 is in public beta, are there any newsgroups available to
> the
> > public (or at least, those with the beta)?
> >
> >
> > Thanks,
> > Adam Machanic
> >
> >
>
public (or at least, those with the beta)?
Thanks,
Adam Machaniconline:
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us
newsreader info is on that web page as well
--
Keith
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> Now that SS2005 is in public beta, are there any newsgroups available to
the
> public (or at least, those with the beta)?
>
> Thanks,
> Adam Machanic
>|||Thanks!
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:usG5rTLdEHA.1040@.TK2MSFTNGP10.phx.gbl...
> online:
>
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us
> newsreader info is on that web page as well
> --
> Keith
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ON%23dLKLdEHA.1048@.tk2msftngp13.phx.gbl...
> > Now that SS2005 is in public beta, are there any newsgroups available to
> the
> > public (or at least, those with the beta)?
> >
> >
> > Thanks,
> > Adam Machanic
> >
> >
>
public server role
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
All logins belong to the public server role. It's like the "Everyone" group in Windows. There is a bug filed against Management Studio for not displaying this role in the list of fixed server roles.
The VIEW ANY DATABASE permission is assigned to public for backward compatibility with SQL Server 2000.
Here are a couple of BOL articles that mention this role:
http://msdn2.microsoft.com/en-us/library/ms175892(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms187096(SQL.90).aspx
Thanks
Laurentiu
The role does not show in sp_helpsrvrole, either. I didn't have time to look for an appropriate catalog view, so there may be one that shows it.
Sharon
|||Catalogs show it: sys.server_principals.
You can see the permissions granted to it in sys.server_permissions.
Thanks
Laurentiu
public role question
Using SS2000 SP4. I'm trying to lock down the database as much as possible.
I
created a new sql server login and gave the user no permissions other than
public role.
Why am I able to create a stored procedure with the new user. I thought
Public had select, insert, update, delete permissions. Does that allow a use
r
to create a stored procedure?
Thanks,
--
Dan D.I ran this query EXEC sp_helprotect NULL, 'public' and saw that public has
the permissions to create Default, create function, create procedure, create
rule, create table, create view, backup database, and backup transaction.
I created a new database and these public did not have these permissions so
I guess public doesn't have them by default.
Is it better to revoke or deny those permissions to public?
Thanks,
--
Dan D.
"Dan D." wrote:
> Using SS2000 SP4. I'm trying to lock down the database as much as possible
. I
> created a new sql server login and gave the user no permissions other than
> public role.
> Why am I able to create a stored procedure with the new user. I thought
> Public had select, insert, update, delete permissions. Does that allow a u
ser
> to create a stored procedure?
> Thanks,
> --
> Dan D.|||In the new database that I created, I was able to create a stored procedure.
I ran EXEC sp_helprotect NULL, 'public' but I don't see the "create
procedure" permisson listed under the action column. How am I able to create
a stored procedure with a user that is only a member of public and has no
other permissions?
--
Dan D.
"Dan D." wrote:
> Using SS2000 SP4. I'm trying to lock down the database as much as possible
. I
> created a new sql server login and gave the user no permissions other than
> public role.
> Why am I able to create a stored procedure with the new user. I thought
> Public had select, insert, update, delete permissions. Does that allow a u
ser
> to create a stored procedure?
> Thanks,
> --
> Dan D.|||What does the query 'SELECT USER' return?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2E69E961-1C77-4B9D-9962-F4C11B7017EB@.microsoft.com...[vbcol=seagreen]
> In the new database that I created, I was able to create a stored
> procedure.
> I ran EXEC sp_helprotect NULL, 'public' but I don't see the "create
> procedure" permisson listed under the action column. How am I able to
> create
> a stored procedure with a user that is only a member of public and has no
> other permissions?
> --
> Dan D.
>
> "Dan D." wrote:
>|||It returns 'dbo'.
--
Dan D.
"Dan Guzman" wrote:
> What does the query 'SELECT USER' return?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:2E69E961-1C77-4B9D-9962-F4C11B7017EB@.microsoft.com...
>
>|||Hi Dan
The user dbo is a privileged user and can create tables and procs, execute
procs, and access data in all the tables. You should try to revoke
permissions from the user dbo.
You have said you created a new user. How are you trying to connect as that
new user?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FB897411-12FA-47C0-BDD8-5855475E1A2C@.microsoft.com...[vbcol=seagreen]
> It returns 'dbo'.
> --
> Dan D.
>
> "Dan Guzman" wrote:
>|||The new user that I created in the new database I connected to through QA an
d
was able to create a stored procedure. If I run EXEC sp_helprotect NULL,
'public', the only permissions that public has is for "select" to 18 system
tables. If I run "select user" (and what does this tell me - who owns the
database?), I get 'dbo'. So I'm wondering how I'm able to create a stored
procedure if public doesn't have permissions and the new user is only in the
public role with no other permissions?
Are you saying that the permissions to create the stored procedure are
coming from dbo so I need to revoke them from 'dbo'?
The ultimate objective is to be able to create a login/user that a web app
will use and the user should only have permissions for "select" to the views
and "execute" on the stored procedures with no permissions on the underlying
tables. Would I need to revoke insert, update, and delete permissions from
public in this case?
Thanks for you help,
--
Dan D.
"Kalen Delaney" wrote:
> Hi Dan
> The user dbo is a privileged user and can create tables and procs, execute
> procs, and access data in all the tables. You should try to revoke
> permissions from the user dbo.
> You have said you created a new user. How are you trying to connect as tha
t
> new user?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:FB897411-12FA-47C0-BDD8-5855475E1A2C@.microsoft.com...
>
>|||If 'select user' is returning DBO, it means you are connecting AS DBO, and
not as your new user.
You ARE the special DBO user, and not the new user, which is why you have
all these permissions. You are not connecting as your new user and not
testing the permissions the new user has.
So again, please tell us exactly HOW you are trying to connect as the new
user. If you are connecting through QA, please tell us what you are putting
in every box in the connection dialog.
(Telling you that dbo 'owns' the database would be meaningless. DBO is a
user name. It is the name always given to whatever login is the owner of the
database, when that login connects to the database. Please read about logins
and user names in the Books Online.)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A193F3D7-B34C-49A8-AA8E-2CA851011AFD@.microsoft.com...[vbcol=seagreen]
> The new user that I created in the new database I connected to through QA
> and
> was able to create a stored procedure. If I run EXEC sp_helprotect NULL,
> 'public', the only permissions that public has is for "select" to 18
> system
> tables. If I run "select user" (and what does this tell me - who owns the
> database?), I get 'dbo'. So I'm wondering how I'm able to create a stored
> procedure if public doesn't have permissions and the new user is only in
> the
> public role with no other permissions?
> Are you saying that the permissions to create the stored procedure are
> coming from dbo so I need to revoke them from 'dbo'?
> The ultimate objective is to be able to create a login/user that a web app
> will use and the user should only have permissions for "select" to the
> views
> and "execute" on the stored procedures with no permissions on the
> underlying
> tables. Would I need to revoke insert, update, and delete permissions from
> public in this case?
> Thanks for you help,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>|||I'm sorry. I didn't understand what Dan was asking me to do. If I log in
through QA using the new user and run "select user" I get "dantest" which is
the new user. Then I am able to create a stored procedure. If I run 'EXEC
sp_helprotect NULL, 'dantest'', I get "There are no matching rows on which t
o
report.". If I run EXEC sp_helprotect NULL, 'public', I get a list of 18
system tables with select permissions.
--
Dan D.
"Kalen Delaney" wrote:
> If 'select user' is returning DBO, it means you are connecting AS DBO, and
> not as your new user.
> You ARE the special DBO user, and not the new user, which is why you have
> all these permissions. You are not connecting as your new user and not
> testing the permissions the new user has.
> So again, please tell us exactly HOW you are trying to connect as the new
> user. If you are connecting through QA, please tell us what you are puttin
g
> in every box in the connection dialog.
> (Telling you that dbo 'owns' the database would be meaningless. DBO is a
> user name. It is the name always given to whatever login is the owner of t
he
> database, when that login connects to the database. Please read about logi
ns
> and user names in the Books Online.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A193F3D7-B34C-49A8-AA8E-2CA851011AFD@.microsoft.com...
>
>|||Sorry I didn't make my instructions clearer. A common cause of elevated
permissions is that users are members of the 'BUILTIN\Administrators'
Windows group and thereby get sysadmin permissions. User 'dbo' would be
returned in that case.
Since 'SELECT USER' returns 'dantest' instead of'dbo', we know 'dantest' is
not a sysadmin role member and is not the database owner. That means one of
the following must be true for the user to create procs:
1) The user is a member of the db_owner role. You can check this with
sp_helprolemember 'db_owner'
2) The user is a member of the db_ddladmin role. You can check this with
sp_helprolemember 'db_ddladmin'
3) The user (or a role the member belongs to) was granted CREATE PROCEDURE
permissions. You can check this with sp_helprotect 'CREATE PROCEDURE'
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:583E27E1-1C79-40D8-A9D2-A22B348D2154@.microsoft.com...[vbcol=seagreen]
> I'm sorry. I didn't understand what Dan was asking me to do. If I log in
> through QA using the new user and run "select user" I get "dantest" which
> is
> the new user. Then I am able to create a stored procedure. If I run 'EXEC
> sp_helprotect NULL, 'dantest'', I get "There are no matching rows on which
> to
> report.". If I run EXEC sp_helprotect NULL, 'public', I get a list of 18
> system tables with select permissions.
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>
I
created a new sql server login and gave the user no permissions other than
public role.
Why am I able to create a stored procedure with the new user. I thought
Public had select, insert, update, delete permissions. Does that allow a use
r
to create a stored procedure?
Thanks,
--
Dan D.I ran this query EXEC sp_helprotect NULL, 'public' and saw that public has
the permissions to create Default, create function, create procedure, create
rule, create table, create view, backup database, and backup transaction.
I created a new database and these public did not have these permissions so
I guess public doesn't have them by default.
Is it better to revoke or deny those permissions to public?
Thanks,
--
Dan D.
"Dan D." wrote:
> Using SS2000 SP4. I'm trying to lock down the database as much as possible
. I
> created a new sql server login and gave the user no permissions other than
> public role.
> Why am I able to create a stored procedure with the new user. I thought
> Public had select, insert, update, delete permissions. Does that allow a u
ser
> to create a stored procedure?
> Thanks,
> --
> Dan D.|||In the new database that I created, I was able to create a stored procedure.
I ran EXEC sp_helprotect NULL, 'public' but I don't see the "create
procedure" permisson listed under the action column. How am I able to create
a stored procedure with a user that is only a member of public and has no
other permissions?
--
Dan D.
"Dan D." wrote:
> Using SS2000 SP4. I'm trying to lock down the database as much as possible
. I
> created a new sql server login and gave the user no permissions other than
> public role.
> Why am I able to create a stored procedure with the new user. I thought
> Public had select, insert, update, delete permissions. Does that allow a u
ser
> to create a stored procedure?
> Thanks,
> --
> Dan D.|||What does the query 'SELECT USER' return?
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2E69E961-1C77-4B9D-9962-F4C11B7017EB@.microsoft.com...[vbcol=seagreen]
> In the new database that I created, I was able to create a stored
> procedure.
> I ran EXEC sp_helprotect NULL, 'public' but I don't see the "create
> procedure" permisson listed under the action column. How am I able to
> create
> a stored procedure with a user that is only a member of public and has no
> other permissions?
> --
> Dan D.
>
> "Dan D." wrote:
>|||It returns 'dbo'.
--
Dan D.
"Dan Guzman" wrote:
> What does the query 'SELECT USER' return?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:2E69E961-1C77-4B9D-9962-F4C11B7017EB@.microsoft.com...
>
>|||Hi Dan
The user dbo is a privileged user and can create tables and procs, execute
procs, and access data in all the tables. You should try to revoke
permissions from the user dbo.
You have said you created a new user. How are you trying to connect as that
new user?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:FB897411-12FA-47C0-BDD8-5855475E1A2C@.microsoft.com...[vbcol=seagreen]
> It returns 'dbo'.
> --
> Dan D.
>
> "Dan Guzman" wrote:
>|||The new user that I created in the new database I connected to through QA an
d
was able to create a stored procedure. If I run EXEC sp_helprotect NULL,
'public', the only permissions that public has is for "select" to 18 system
tables. If I run "select user" (and what does this tell me - who owns the
database?), I get 'dbo'. So I'm wondering how I'm able to create a stored
procedure if public doesn't have permissions and the new user is only in the
public role with no other permissions?
Are you saying that the permissions to create the stored procedure are
coming from dbo so I need to revoke them from 'dbo'?
The ultimate objective is to be able to create a login/user that a web app
will use and the user should only have permissions for "select" to the views
and "execute" on the stored procedures with no permissions on the underlying
tables. Would I need to revoke insert, update, and delete permissions from
public in this case?
Thanks for you help,
--
Dan D.
"Kalen Delaney" wrote:
> Hi Dan
> The user dbo is a privileged user and can create tables and procs, execute
> procs, and access data in all the tables. You should try to revoke
> permissions from the user dbo.
> You have said you created a new user. How are you trying to connect as tha
t
> new user?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:FB897411-12FA-47C0-BDD8-5855475E1A2C@.microsoft.com...
>
>|||If 'select user' is returning DBO, it means you are connecting AS DBO, and
not as your new user.
You ARE the special DBO user, and not the new user, which is why you have
all these permissions. You are not connecting as your new user and not
testing the permissions the new user has.
So again, please tell us exactly HOW you are trying to connect as the new
user. If you are connecting through QA, please tell us what you are putting
in every box in the connection dialog.
(Telling you that dbo 'owns' the database would be meaningless. DBO is a
user name. It is the name always given to whatever login is the owner of the
database, when that login connects to the database. Please read about logins
and user names in the Books Online.)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A193F3D7-B34C-49A8-AA8E-2CA851011AFD@.microsoft.com...[vbcol=seagreen]
> The new user that I created in the new database I connected to through QA
> and
> was able to create a stored procedure. If I run EXEC sp_helprotect NULL,
> 'public', the only permissions that public has is for "select" to 18
> system
> tables. If I run "select user" (and what does this tell me - who owns the
> database?), I get 'dbo'. So I'm wondering how I'm able to create a stored
> procedure if public doesn't have permissions and the new user is only in
> the
> public role with no other permissions?
> Are you saying that the permissions to create the stored procedure are
> coming from dbo so I need to revoke them from 'dbo'?
> The ultimate objective is to be able to create a login/user that a web app
> will use and the user should only have permissions for "select" to the
> views
> and "execute" on the stored procedures with no permissions on the
> underlying
> tables. Would I need to revoke insert, update, and delete permissions from
> public in this case?
> Thanks for you help,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>|||I'm sorry. I didn't understand what Dan was asking me to do. If I log in
through QA using the new user and run "select user" I get "dantest" which is
the new user. Then I am able to create a stored procedure. If I run 'EXEC
sp_helprotect NULL, 'dantest'', I get "There are no matching rows on which t
o
report.". If I run EXEC sp_helprotect NULL, 'public', I get a list of 18
system tables with select permissions.
--
Dan D.
"Kalen Delaney" wrote:
> If 'select user' is returning DBO, it means you are connecting AS DBO, and
> not as your new user.
> You ARE the special DBO user, and not the new user, which is why you have
> all these permissions. You are not connecting as your new user and not
> testing the permissions the new user has.
> So again, please tell us exactly HOW you are trying to connect as the new
> user. If you are connecting through QA, please tell us what you are puttin
g
> in every box in the connection dialog.
> (Telling you that dbo 'owns' the database would be meaningless. DBO is a
> user name. It is the name always given to whatever login is the owner of t
he
> database, when that login connects to the database. Please read about logi
ns
> and user names in the Books Online.)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A193F3D7-B34C-49A8-AA8E-2CA851011AFD@.microsoft.com...
>
>|||Sorry I didn't make my instructions clearer. A common cause of elevated
permissions is that users are members of the 'BUILTIN\Administrators'
Windows group and thereby get sysadmin permissions. User 'dbo' would be
returned in that case.
Since 'SELECT USER' returns 'dantest' instead of'dbo', we know 'dantest' is
not a sysadmin role member and is not the database owner. That means one of
the following must be true for the user to create procs:
1) The user is a member of the db_owner role. You can check this with
sp_helprolemember 'db_owner'
2) The user is a member of the db_ddladmin role. You can check this with
sp_helprolemember 'db_ddladmin'
3) The user (or a role the member belongs to) was granted CREATE PROCEDURE
permissions. You can check this with sp_helprotect 'CREATE PROCEDURE'
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:583E27E1-1C79-40D8-A9D2-A22B348D2154@.microsoft.com...[vbcol=seagreen]
> I'm sorry. I didn't understand what Dan was asking me to do. If I log in
> through QA using the new user and run "select user" I get "dantest" which
> is
> the new user. Then I am able to create a stored procedure. If I run 'EXEC
> sp_helprotect NULL, 'dantest'', I get "There are no matching rows on which
> to
> report.". If I run EXEC sp_helprotect NULL, 'public', I get a list of 18
> system tables with select permissions.
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>
public role problem
Hi all
I had a database that was copied over from one server to another say SERVER1
to SERVER2. As always it happens that the permissions too get carried over
with the database. I was able to remove the carried over logins from all the
roles but not from public. So now I am not able to create the same login on
SERVER2 as on SERVER1 since it tells me that the login is already existing
in the database as a role.
I do understand that no members can be removed from public.. but is there
someway I can bypass this to create the same logins on SERVER2 as on SERVER1
' Or is there something wrong that I am doing while copying the files..
should I be taking some other precautions when copying over.. '
Any help is appreciated.. Thank you
NEVILLEDropping the login on server level or the user on db level will remove the
membership of the login/user in public.
Have a look at http://support.microsoft.com/view/tn.asp?kb=246133
hth
Quentin
"Neville" <george@.synygy.com> wrote in message
news:uD5lkGT2DHA.2528@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I had a database that was copied over from one server to another say
SERVER1
> to SERVER2. As always it happens that the permissions too get carried over
> with the database. I was able to remove the carried over logins from all
the
> roles but not from public. So now I am not able to create the same login
on
> SERVER2 as on SERVER1 since it tells me that the login is already existing
> in the database as a role.
> I do understand that no members can be removed from public.. but is there
> someway I can bypass this to create the same logins on SERVER2 as on
SERVER1
> ' Or is there something wrong that I am doing while copying the files..
> should I be taking some other precautions when copying over.. '
> Any help is appreciated.. Thank you
> NEVILLE
>|||Sorry for not specifying something that I just noticed.. I do not see the
carried over logins in the users list of the database through Enterprise
Manager.. However, querying the database shows that the users exist.. I
tried refreshing the screen, reconnecting to the server and a lot of other
things but it still does not show me the users through Enterprise manager.
Funny though, one of my colleagues connected to the server from his desktop
with the same login and was able to see all the carried over logins.. which
rounds off to one thing that the problem should be with something on my
desktop.. However, I did not find any settings different from that on his
desktop..
And btw thanks for the help with the link..
NEVILLE
"Quentin Ran" <ab@.who.com> wrote in message
news:eOVpnlT2DHA.3436@.tk2msftngp13.phx.gbl...
> Dropping the login on server level or the user on db level will remove the
> membership of the login/user in public.
> Have a look at http://support.microsoft.com/view/tn.asp?kb=246133
> hth
> Quentin
>
> "Neville" <george@.synygy.com> wrote in message
> news:uD5lkGT2DHA.2528@.TK2MSFTNGP10.phx.gbl...
> > Hi all
> >
> > I had a database that was copied over from one server to another say
> SERVER1
> > to SERVER2. As always it happens that the permissions too get carried
over
> > with the database. I was able to remove the carried over logins from all
> the
> > roles but not from public. So now I am not able to create the same login
> on
> > SERVER2 as on SERVER1 since it tells me that the login is already
existing
> > in the database as a role.
> >
> > I do understand that no members can be removed from public.. but is
there
> > someway I can bypass this to create the same logins on SERVER2 as on
> SERVER1
> > ' Or is there something wrong that I am doing while copying the files..
> > should I be taking some other precautions when copying over.. '
> >
> > Any help is appreciated.. Thank you
> >
> > NEVILLE
> >
> >
>
I had a database that was copied over from one server to another say SERVER1
to SERVER2. As always it happens that the permissions too get carried over
with the database. I was able to remove the carried over logins from all the
roles but not from public. So now I am not able to create the same login on
SERVER2 as on SERVER1 since it tells me that the login is already existing
in the database as a role.
I do understand that no members can be removed from public.. but is there
someway I can bypass this to create the same logins on SERVER2 as on SERVER1
' Or is there something wrong that I am doing while copying the files..
should I be taking some other precautions when copying over.. '
Any help is appreciated.. Thank you
NEVILLEDropping the login on server level or the user on db level will remove the
membership of the login/user in public.
Have a look at http://support.microsoft.com/view/tn.asp?kb=246133
hth
Quentin
"Neville" <george@.synygy.com> wrote in message
news:uD5lkGT2DHA.2528@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I had a database that was copied over from one server to another say
SERVER1
> to SERVER2. As always it happens that the permissions too get carried over
> with the database. I was able to remove the carried over logins from all
the
> roles but not from public. So now I am not able to create the same login
on
> SERVER2 as on SERVER1 since it tells me that the login is already existing
> in the database as a role.
> I do understand that no members can be removed from public.. but is there
> someway I can bypass this to create the same logins on SERVER2 as on
SERVER1
> ' Or is there something wrong that I am doing while copying the files..
> should I be taking some other precautions when copying over.. '
> Any help is appreciated.. Thank you
> NEVILLE
>|||Sorry for not specifying something that I just noticed.. I do not see the
carried over logins in the users list of the database through Enterprise
Manager.. However, querying the database shows that the users exist.. I
tried refreshing the screen, reconnecting to the server and a lot of other
things but it still does not show me the users through Enterprise manager.
Funny though, one of my colleagues connected to the server from his desktop
with the same login and was able to see all the carried over logins.. which
rounds off to one thing that the problem should be with something on my
desktop.. However, I did not find any settings different from that on his
desktop..
And btw thanks for the help with the link..
NEVILLE
"Quentin Ran" <ab@.who.com> wrote in message
news:eOVpnlT2DHA.3436@.tk2msftngp13.phx.gbl...
> Dropping the login on server level or the user on db level will remove the
> membership of the login/user in public.
> Have a look at http://support.microsoft.com/view/tn.asp?kb=246133
> hth
> Quentin
>
> "Neville" <george@.synygy.com> wrote in message
> news:uD5lkGT2DHA.2528@.TK2MSFTNGP10.phx.gbl...
> > Hi all
> >
> > I had a database that was copied over from one server to another say
> SERVER1
> > to SERVER2. As always it happens that the permissions too get carried
over
> > with the database. I was able to remove the carried over logins from all
> the
> > roles but not from public. So now I am not able to create the same login
> on
> > SERVER2 as on SERVER1 since it tells me that the login is already
existing
> > in the database as a role.
> >
> > I do understand that no members can be removed from public.. but is
there
> > someway I can bypass this to create the same logins on SERVER2 as on
> SERVER1
> > ' Or is there something wrong that I am doing while copying the files..
> > should I be taking some other precautions when copying over.. '
> >
> > Any help is appreciated.. Thank you
> >
> > NEVILLE
> >
> >
>
public role problem
Hi all
I had a database that was copied over from one server to another say SERVER1
to SERVER2. As always it happens that the permissions too get carried over
with the database. I was able to remove the carried over logins from all the
roles but not from public. So now I am not able to create the same login on
SERVER2 as on SERVER1 since it tells me that the login is already existing
in the database as a role.
I do understand that no members can be removed from public.. but is there
someway I can bypass this to create the same logins on SERVER2 as on SERVER1
' Or is there something wrong that I am doing while copying the files..
should I be taking some other precautions when copying over.. '
Any help is appreciated.. Thank you
NEVILLEDropping the login on server level or the user on db level will remove the
membership of the login/user in public.
Have a look at http://support.microsoft.com/view/tn.asp?kb=246133
hth
Quentin
"Neville" <george@.synygy.com> wrote in message
news:uD5lkGT2DHA.2528@.TK2MSFTNGP10.phx.gbl...
SERVER1
the
on
SERVER1
carried over logins in the users list of the database through Enterprise
Manager.. However, querying the database shows that the users exist.. I
tried refreshing the screen, reconnecting to the server and a lot of other
things but it still does not show me the users through Enterprise manager.
Funny though, one of my colleagues connected to the server from his desktop
with the same login and was able to see all the carried over logins.. which
rounds off to one thing that the problem should be with something on my
desktop.. However, I did not find any settings different from that on his
desktop..
And btw thanks for the help with the link..
NEVILLE
"Quentin Ran" <ab@.who.com> wrote in message
news:eOVpnlT2DHA.3436@.tk2msftngp13.phx.gbl...
I had a database that was copied over from one server to another say SERVER1
to SERVER2. As always it happens that the permissions too get carried over
with the database. I was able to remove the carried over logins from all the
roles but not from public. So now I am not able to create the same login on
SERVER2 as on SERVER1 since it tells me that the login is already existing
in the database as a role.
I do understand that no members can be removed from public.. but is there
someway I can bypass this to create the same logins on SERVER2 as on SERVER1
' Or is there something wrong that I am doing while copying the files..
should I be taking some other precautions when copying over.. '
Any help is appreciated.. Thank you
NEVILLEDropping the login on server level or the user on db level will remove the
membership of the login/user in public.
Have a look at http://support.microsoft.com/view/tn.asp?kb=246133
hth
Quentin
"Neville" <george@.synygy.com> wrote in message
news:uD5lkGT2DHA.2528@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi all
> I had a database that was copied over from one server to another say
SERVER1
quote:
> to SERVER2. As always it happens that the permissions too get carried over
> with the database. I was able to remove the carried over logins from all
the
quote:
> roles but not from public. So now I am not able to create the same login
on
quote:
> SERVER2 as on SERVER1 since it tells me that the login is already existing
> in the database as a role.
> I do understand that no members can be removed from public.. but is there
> someway I can bypass this to create the same logins on SERVER2 as on
SERVER1
quote:|||Sorry for not specifying something that I just noticed.. I do not see the
> ' Or is there something wrong that I am doing while copying the files..
> should I be taking some other precautions when copying over.. '
> Any help is appreciated.. Thank you
> NEVILLE
>
carried over logins in the users list of the database through Enterprise
Manager.. However, querying the database shows that the users exist.. I
tried refreshing the screen, reconnecting to the server and a lot of other
things but it still does not show me the users through Enterprise manager.
Funny though, one of my colleagues connected to the server from his desktop
with the same login and was able to see all the carried over logins.. which
rounds off to one thing that the problem should be with something on my
desktop.. However, I did not find any settings different from that on his
desktop..
And btw thanks for the help with the link..
NEVILLE
"Quentin Ran" <ab@.who.com> wrote in message
news:eOVpnlT2DHA.3436@.tk2msftngp13.phx.gbl...
quote:
> Dropping the login on server level or the user on db level will remove the
> membership of the login/user in public.
> Have a look at http://support.microsoft.com/view/tn.asp?kb=246133
> hth
> Quentin
>
> "Neville" <george@.synygy.com> wrote in message
> news:uD5lkGT2DHA.2528@.TK2MSFTNGP10.phx.gbl...
> SERVER1
over[QUOTE]
> the
> on
existing[QUOTE]
there[QUOTE]
> SERVER1
>
Public Role Permissions
I have a system that before I arrived, they granted all permissions to all
objects to the public role instead of creating there own. While in the
process of trying to fix this we discovered that all users through the publi
c
role have the ability to create objects.
Ultimately what I need to know is how do I revoke or remove permissions to
the public role?Hi,
You will need to create new roles and assign the required permissions the
role. After that assign the approprate roles to users.
Once you are done with that then you could use ROVOKE command remove the
permissions from Public role. The only way to rovoke
Public role rights is to Rove the permissions.
Thanks
Hari
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:43353E72-E45B-4048-BA73-E84000DFEA4D@.microsoft.com...
>I have a system that before I arrived, they granted all permissions to all
> objects to the public role instead of creating there own. While in the
> process of trying to fix this we discovered that all users through the
> public
> role have the ability to create objects.
> Ultimately what I need to know is how do I revoke or remove permissions to
> the public role?
objects to the public role instead of creating there own. While in the
process of trying to fix this we discovered that all users through the publi
c
role have the ability to create objects.
Ultimately what I need to know is how do I revoke or remove permissions to
the public role?Hi,
You will need to create new roles and assign the required permissions the
role. After that assign the approprate roles to users.
Once you are done with that then you could use ROVOKE command remove the
permissions from Public role. The only way to rovoke
Public role rights is to Rove the permissions.
Thanks
Hari
SQL Server MVP
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:43353E72-E45B-4048-BA73-E84000DFEA4D@.microsoft.com...
>I have a system that before I arrived, they granted all permissions to all
> objects to the public role instead of creating there own. While in the
> process of trying to fix this we discovered that all users through the
> public
> role have the ability to create objects.
> Ultimately what I need to know is how do I revoke or remove permissions to
> the public role?
Monday, February 20, 2012
public role membership
why does
sp_helprolemember 'public'
show no results, even though through the gui you can see that all the users
have tat role?Jason,
Since 'public' is defined to always contain everyone with access to a
database, there is no specific assignment to make them role members. So
sp_helprolemember has no rows to read and report on.
FWIW, if I assign a Windows Group to be a role member, only the Windows
Group (but not the logins that are members of the Windows Group) are
reported on by sp_helprolemember.
RLF
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
> why does
> sp_helprolemember 'public'
> show no results, even though through the gui you can see that all the
> users
> have tat role?|||thank you
"Russell Fields" wrote:
> Jason,
> Since 'public' is defined to always contain everyone with access to a
> database, there is no specific assignment to make them role members. So
> sp_helprolemember has no rows to read and report on.
> FWIW, if I assign a Windows Group to be a role member, only the Windows
> Group (but not the logins that are members of the Windows Group) are
> reported on by sp_helprolemember.
> RLF
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
>
>
sp_helprolemember 'public'
show no results, even though through the gui you can see that all the users
have tat role?Jason,
Since 'public' is defined to always contain everyone with access to a
database, there is no specific assignment to make them role members. So
sp_helprolemember has no rows to read and report on.
FWIW, if I assign a Windows Group to be a role member, only the Windows
Group (but not the logins that are members of the Windows Group) are
reported on by sp_helprolemember.
RLF
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
> why does
> sp_helprolemember 'public'
> show no results, even though through the gui you can see that all the
> users
> have tat role?|||thank you
"Russell Fields" wrote:
> Jason,
> Since 'public' is defined to always contain everyone with access to a
> database, there is no specific assignment to make them role members. So
> sp_helprolemember has no rows to read and report on.
> FWIW, if I assign a Windows Group to be a role member, only the Windows
> Group (but not the logins that are members of the Windows Group) are
> reported on by sp_helprolemember.
> RLF
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
>
>
public role membership
why does
sp_helprolemember 'public'
show no results, even though through the gui you can see that all the users
have tat role?Jason,
Since 'public' is defined to always contain everyone with access to a
database, there is no specific assignment to make them role members. So
sp_helprolemember has no rows to read and report on.
FWIW, if I assign a Windows Group to be a role member, only the Windows
Group (but not the logins that are members of the Windows Group) are
reported on by sp_helprolemember.
RLF
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
> why does
> sp_helprolemember 'public'
> show no results, even though through the gui you can see that all the
> users
> have tat role?|||thank you
"Russell Fields" wrote:
> Jason,
> Since 'public' is defined to always contain everyone with access to a
> database, there is no specific assignment to make them role members. So
> sp_helprolemember has no rows to read and report on.
> FWIW, if I assign a Windows Group to be a role member, only the Windows
> Group (but not the logins that are members of the Windows Group) are
> reported on by sp_helprolemember.
> RLF
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
> > why does
> >
> > sp_helprolemember 'public'
> >
> > show no results, even though through the gui you can see that all the
> > users
> > have tat role?
>
>
sp_helprolemember 'public'
show no results, even though through the gui you can see that all the users
have tat role?Jason,
Since 'public' is defined to always contain everyone with access to a
database, there is no specific assignment to make them role members. So
sp_helprolemember has no rows to read and report on.
FWIW, if I assign a Windows Group to be a role member, only the Windows
Group (but not the logins that are members of the Windows Group) are
reported on by sp_helprolemember.
RLF
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
> why does
> sp_helprolemember 'public'
> show no results, even though through the gui you can see that all the
> users
> have tat role?|||thank you
"Russell Fields" wrote:
> Jason,
> Since 'public' is defined to always contain everyone with access to a
> database, there is no specific assignment to make them role members. So
> sp_helprolemember has no rows to read and report on.
> FWIW, if I assign a Windows Group to be a role member, only the Windows
> Group (but not the logins that are members of the Windows Group) are
> reported on by sp_helprolemember.
> RLF
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
> > why does
> >
> > sp_helprolemember 'public'
> >
> > show no results, even though through the gui you can see that all the
> > users
> > have tat role?
>
>
public role membership
why does
sp_helprolemember 'public'
show no results, even though through the gui you can see that all the users
have tat role?
Jason,
Since 'public' is defined to always contain everyone with access to a
database, there is no specific assignment to make them role members. So
sp_helprolemember has no rows to read and report on.
FWIW, if I assign a Windows Group to be a role member, only the Windows
Group (but not the logins that are members of the Windows Group) are
reported on by sp_helprolemember.
RLF
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
> why does
> sp_helprolemember 'public'
> show no results, even though through the gui you can see that all the
> users
> have tat role?
|||thank you
"Russell Fields" wrote:
> Jason,
> Since 'public' is defined to always contain everyone with access to a
> database, there is no specific assignment to make them role members. So
> sp_helprolemember has no rows to read and report on.
> FWIW, if I assign a Windows Group to be a role member, only the Windows
> Group (but not the logins that are members of the Windows Group) are
> reported on by sp_helprolemember.
> RLF
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
>
>
sp_helprolemember 'public'
show no results, even though through the gui you can see that all the users
have tat role?
Jason,
Since 'public' is defined to always contain everyone with access to a
database, there is no specific assignment to make them role members. So
sp_helprolemember has no rows to read and report on.
FWIW, if I assign a Windows Group to be a role member, only the Windows
Group (but not the logins that are members of the Windows Group) are
reported on by sp_helprolemember.
RLF
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
> why does
> sp_helprolemember 'public'
> show no results, even though through the gui you can see that all the
> users
> have tat role?
|||thank you
"Russell Fields" wrote:
> Jason,
> Since 'public' is defined to always contain everyone with access to a
> database, there is no specific assignment to make them role members. So
> sp_helprolemember has no rows to read and report on.
> FWIW, if I assign a Windows Group to be a role member, only the Windows
> Group (but not the logins that are members of the Windows Group) are
> reported on by sp_helprolemember.
> RLF
> "Jason" <Jason@.discussions.microsoft.com> wrote in message
> news:FADD06B3-9D06-4930-93B0-4CA7C26B1A1F@.microsoft.com...
>
>
Public Role in SQL Server 2000 SP3
If I add a user to a database with Public Database Role, I
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.
The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.
|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.
|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks
>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
Role, I[vbcol=seagreen]
him.[vbcol=seagreen]
any
>
>.
>
|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any
|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.
The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.
|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.
|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks
>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
Role, I[vbcol=seagreen]
him.[vbcol=seagreen]
any
>
>.
>
|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any
|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any
Public Role in SQL Server 2000 SP3
If I add a user to a database with Public Database Role, I
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks
>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
>> If I add a user to a database with Public Database
Role, I
>> would like to know what privilege has been granted to
him.
>> This is because we find that he is not able to SELECT
any
>> table.
>
>.
>|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> >--Original Message--
> >The default permissions of the public role allow SELECT
> from sysobjects and
> >other meta-data objects.
> >
> >A security best practice is to create your own roles and
> grant object
> >permissions to those roles as needed. You can then
> control security by user
> >role membership.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> >> If I add a user to a database with Public Database
> Role, I
> >> would like to know what privilege has been granted to
> him.
> >>
> >> This is because we find that he is not able to SELECT
> any
> >> table.
> >
> >
> >.
> >|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> >--Original Message--
> >The default permissions of the public role allow SELECT
> from sysobjects and
> >other meta-data objects.
> >
> >A security best practice is to create your own roles and
> grant object
> >permissions to those roles as needed. You can then
> control security by user
> >role membership.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> >> If I add a user to a database with Public Database
> Role, I
> >> would like to know what privilege has been granted to
> him.
> >>
> >> This is because we find that he is not able to SELECT
> any
> >> table.
> >
> >
> >.
> >
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks
>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
>> If I add a user to a database with Public Database
Role, I
>> would like to know what privilege has been granted to
him.
>> This is because we find that he is not able to SELECT
any
>> table.
>
>.
>|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> >--Original Message--
> >The default permissions of the public role allow SELECT
> from sysobjects and
> >other meta-data objects.
> >
> >A security best practice is to create your own roles and
> grant object
> >permissions to those roles as needed. You can then
> control security by user
> >role membership.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> >> If I add a user to a database with Public Database
> Role, I
> >> would like to know what privilege has been granted to
> him.
> >>
> >> This is because we find that he is not able to SELECT
> any
> >> table.
> >
> >
> >.
> >|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx.gbl...
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
> >--Original Message--
> >The default permissions of the public role allow SELECT
> from sysobjects and
> >other meta-data objects.
> >
> >A security best practice is to create your own roles and
> grant object
> >permissions to those roles as needed. You can then
> control security by user
> >role membership.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2b73701c46890$d144c1a0$a501280a@.phx.gbl...
> >> If I add a user to a database with Public Database
> Role, I
> >> would like to know what privilege has been granted to
> him.
> >>
> >> This is because we find that he is not able to SELECT
> any
> >> table.
> >
> >
> >.
> >
Public Role in SQL Server 2000 SP3
If I add a user to a database with Public Database Role, I
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx
.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx
.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks
>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2b73701c46890$d144c1a0$a501280a@.phx
.gbl...
Role, I[vbcol=seagreen]
him.[vbcol=seagreen]
any[vbcol=seagreen]
>
>.
>|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx
.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
>
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx
.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
>
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any
would like to know what privilege has been granted to him.
This is because we find that he is not able to SELECT any
table.The default permissions of the public role allow SELECT from sysobjects and
other meta-data objects.
A security best practice is to create your own roles and grant object
permissions to those roles as needed. You can then control security by user
role membership.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx
.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||Hi,
The public role is a special database role to which every database user
belongs. Captures all default permissions for users in a database.
The user with public role can access only the tables / objects granted
permissions exclusevely to PUBLIC role.
For eg:
Hari is a database owner and he create a table customer. After creation he
gave the below statement to give previlage to customer table.
GRANT select on customer to public
IN this case user inside public role will be able to access the table
customer. Otherwise he can not.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2b73701c46890$d144c1a0$a501280a@.phx
.gbl...
> If I add a user to a database with Public Database Role, I
> would like to know what privilege has been granted to him.
> This is because we find that he is not able to SELECT any
> table.|||If I want him to select all tables, what is the easiest
way to achieve it ?
Thanks
>--Original Message--
>The default permissions of the public role allow SELECT
from sysobjects and
>other meta-data objects.
>A security best practice is to create your own roles and
grant object
>permissions to those roles as needed. You can then
control security by user
>role membership.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2b73701c46890$d144c1a0$a501280a@.phx
.gbl...
Role, I[vbcol=seagreen]
him.[vbcol=seagreen]
any[vbcol=seagreen]
>
>.
>|||HI,
You can assign the user "db_datareader" database fixed role. This will
enable the user to select from all tables
from the particular database.
How to add the role.
sp_addrolemember 'db_datareader','user_name'
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx
.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
>
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any|||To add to Hari's response, db_datareader fixed database role members can
select from any table or view. However, execute permissions on read-only
stored procedures are not included.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2ba7c01c46894$c6c84a40$a401280a@.phx
.gbl...[vbcol=seagreen]
> If I want him to select all tables, what is the easiest
> way to achieve it ?
> Thanks
>
> from sysobjects and
> grant object
> control security by user
> message
> Role, I
> him.
> any
Public role granted to user in master database.
In one of our sql server 2000 databases, there is an
application user that has public granted to it in the
master database. Can this be a security issue? There are
no permissions granted on objects just the public role.
I'm awaiting feedback from the vendor to find out why the
user is there. I just want to make sure this user cannot
do any harm to the master database.Not sure what you are saying. Every database has a role named public, you ca
nnot remove this...
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Erin" <Erin_Regotti@.grainger.com> wrote in message news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
account, I believe).
Russell Fields
"Erin" <Erin_Regotti@.grainger.com> wrote in message
news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
application user that has public granted to it in the
master database. Can this be a security issue? There are
no permissions granted on objects just the public role.
I'm awaiting feedback from the vendor to find out why the
user is there. I just want to make sure this user cannot
do any harm to the master database.Not sure what you are saying. Every database has a role named public, you ca
nnot remove this...
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Erin" <Erin_Regotti@.grainger.com> wrote in message news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
quote:|||And everybody has public access to the master database (via the guest
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.
account, I believe).
Russell Fields
"Erin" <Erin_Regotti@.grainger.com> wrote in message
news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
quote:
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.
Public role granted to user in master database.
In one of our sql server 2000 databases, there is an
application user that has public granted to it in the
master database. Can this be a security issue? There are
no permissions granted on objects just the public role.
I'm awaiting feedback from the vendor to find out why the
user is there. I just want to make sure this user cannot
do any harm to the master database.Not sure what you are saying. Every database has a role named public, you cannot remove this...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Erin" <Erin_Regotti@.grainger.com> wrote in message news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.|||And everybody has public access to the master database (via the guest
account, I believe).
Russell Fields
"Erin" <Erin_Regotti@.grainger.com> wrote in message
news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.
application user that has public granted to it in the
master database. Can this be a security issue? There are
no permissions granted on objects just the public role.
I'm awaiting feedback from the vendor to find out why the
user is there. I just want to make sure this user cannot
do any harm to the master database.Not sure what you are saying. Every database has a role named public, you cannot remove this...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Erin" <Erin_Regotti@.grainger.com> wrote in message news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.|||And everybody has public access to the master database (via the guest
account, I believe).
Russell Fields
"Erin" <Erin_Regotti@.grainger.com> wrote in message
news:a28701c3eb2d$f399e270$a601280a@.phx.gbl...
> In one of our sql server 2000 databases, there is an
> application user that has public granted to it in the
> master database. Can this be a security issue? There are
> no permissions granted on objects just the public role.
> I'm awaiting feedback from the vendor to find out why the
> user is there. I just want to make sure this user cannot
> do any harm to the master database.
Public Role for SQL 2000
For SQL 2000, I see that the Public role has some privileges like SELECT,
UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
(Tables, views, &Stored procedures .)
Is that a security concern?
What will happen if I remove these privileges or revoke them?
Will that effect the rest of the users?
I read many threads on the internet, but no one could tell me the answers
for the 3 questions above.
Thanks in advance.wit1 (wit1@.hotmail.com) writes:
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
That is not the default.
> Is that a security concern?
It does not sound like the best security to me.
> What will happen if I remove these privileges or revoke them?
Impossible to tell as it depends on the application using the database.
> Will that effect the rest of the users?
Again, that depends on the application using the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, I too have same questions.
I tried to drop this public role but not able to drop.
I tried to Revoke Select permission but not able to do that. It is not
giving any error but not working as expected. Any urgent reply will be
gratly appreciated.
Thank,
Tushar Vaja
"wit1" <wit1@.hotmail.com> wrote in message
news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
>
> Is that a security concern?
> What will happen if I remove these privileges or revoke them?
> Will that effect the rest of the users?
>
> I read many threads on the internet, but no one could tell me the answers
> for the 3 questions above.
> Thanks in advance.
>
>|||> Hi, I too have same questions.
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
> Thank,
> Tushar Vaja
>
> "wit1" <wit1@.hotmail.com> wrote in message
> news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
You could try DENY, but since everyone is in public, this could have
the undesired effect that no one can access anything.
But maybe there is some misunderstanding? Could you clarify more precisely
what tables that are accessible to users who should not get there?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, thanks for reply. My problem is below:
Whenever user run my exe on his system, my app will create two DB on his
local system. 1 DB contain general info and second DB contain some very
confidenmt info. Now i do not want user(the person who has installed my app)
to access the second DB but he can access 1st DB if he wants.
I do not knwo how to implemet this thing. Please help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99337CC774783Yazorman@.127.0.0.1...
> Tushar (tushar_vaja@.yahoo.co.in) writes:
> You could try DENY, but since everyone is in public, this could have
> the undesired effect that no one can access anything.
> But maybe there is some misunderstanding? Could you clarify more precisely
> what tables that are accessible to users who should not get there?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> Whenever user run my exe on his system, my app will create two DB on his
> local system. 1 DB contain general info and second DB contain some very
> confidenmt info. Now i do not want user(the person who has installed my
> app) to access the second DB but he can access 1st DB if he wants. I do
> not knwo how to implemet this thing. Please help.
Presumably the user who installed the application will have admin rights
on the machine, and you cannot hide anything from an administrator.
You can of course, store the data in the database encrypted, and then
your application could decrypt the data as needed. But since the
application would have to hide the encryption key somewhere, it's not
safe from a user who is dead set from accessing the data, but at least
it protects you from the stray wanderer.
In any case, you need to cover this situation in the license agreement,
and explicitly say that disclosing the data in the database is not
permitted.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
The following excerpt is taken from Chapter 5 - Microsoft SQL Server 2000
Security of Microsoft SQL Server 2000 Administrator's Pocket Consultant (ISB
N
0-7356-1129-7):
public is the default role for all database users. Users inherit the
permissions and privileges of the public role, and this role represents
their minimum permissions and privileges. Any role that you assign to a
user, beyond the public role, add permissions and privileges. If you want
all database users to have specific permissions, assign the permissions to
the public role.
The following excerpt is taken from Chapter 8 - Managing SQL Serer 2005
Security of Microsoft SQL Server 2005 Administrator's Pocket Consultant:
The guest user is a special user that you can add to a database to allow
anyone with a valid SQL Server login to access the database... Before using
the guest user, you should not the following information about the account:
The guest user is a member of the public server role and inherits the
permissions of this role.
The guest user must exist in a database before anyone can access it as a
guest.
The guest user is used only when a user account has access to SQL Server
but does not have access to the database through this user account.
Other topics around public server role that may be of interest include:
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
http://www.microsoft.com/technet/pr...n/sp3sec01.mspx
http://www.microsoft.com/technet/pr...ploy/mysql.mspx
10 Steps to help Secure SQL Server 2000
https://www.microsoft.com/sql/prodi...n/sp3sec04.mspx
SQL Server 2000 SP3 Security Features and Best Practices: Implementation of
Server Level Security and Object Level Security
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
SQL Server 2005 (BOL) - Security Considerations for a SQL Server Installatio
n
http://msdn2.microsoft.com/en-us/library/ms144228.aspx
Regards,
Keith Wilson
Disclaimer: this posting is provided "as is" without implied or express
warranties.
"Tushar" wrote:
>
>
UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
(Tables, views, &Stored procedures .)
Is that a security concern?
What will happen if I remove these privileges or revoke them?
Will that effect the rest of the users?
I read many threads on the internet, but no one could tell me the answers
for the 3 questions above.
Thanks in advance.wit1 (wit1@.hotmail.com) writes:
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
That is not the default.
> Is that a security concern?
It does not sound like the best security to me.
> What will happen if I remove these privileges or revoke them?
Impossible to tell as it depends on the application using the database.
> Will that effect the rest of the users?
Again, that depends on the application using the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, I too have same questions.
I tried to drop this public role but not able to drop.
I tried to Revoke Select permission but not able to do that. It is not
giving any error but not working as expected. Any urgent reply will be
gratly appreciated.
Thank,
Tushar Vaja
"wit1" <wit1@.hotmail.com> wrote in message
news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
>
> Is that a security concern?
> What will happen if I remove these privileges or revoke them?
> Will that effect the rest of the users?
>
> I read many threads on the internet, but no one could tell me the answers
> for the 3 questions above.
> Thanks in advance.
>
>|||> Hi, I too have same questions.
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
> Thank,
> Tushar Vaja
>
> "wit1" <wit1@.hotmail.com> wrote in message
> news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
You could try DENY, but since everyone is in public, this could have
the undesired effect that no one can access anything.
But maybe there is some misunderstanding? Could you clarify more precisely
what tables that are accessible to users who should not get there?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, thanks for reply. My problem is below:
Whenever user run my exe on his system, my app will create two DB on his
local system. 1 DB contain general info and second DB contain some very
confidenmt info. Now i do not want user(the person who has installed my app)
to access the second DB but he can access 1st DB if he wants.
I do not knwo how to implemet this thing. Please help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99337CC774783Yazorman@.127.0.0.1...
> Tushar (tushar_vaja@.yahoo.co.in) writes:
> You could try DENY, but since everyone is in public, this could have
> the undesired effect that no one can access anything.
> But maybe there is some misunderstanding? Could you clarify more precisely
> what tables that are accessible to users who should not get there?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> Whenever user run my exe on his system, my app will create two DB on his
> local system. 1 DB contain general info and second DB contain some very
> confidenmt info. Now i do not want user(the person who has installed my
> app) to access the second DB but he can access 1st DB if he wants. I do
> not knwo how to implemet this thing. Please help.
Presumably the user who installed the application will have admin rights
on the machine, and you cannot hide anything from an administrator.
You can of course, store the data in the database encrypted, and then
your application could decrypt the data as needed. But since the
application would have to hide the encryption key somewhere, it's not
safe from a user who is dead set from accessing the data, but at least
it protects you from the stray wanderer.
In any case, you need to cover this situation in the license agreement,
and explicitly say that disclosing the data in the database is not
permitted.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
The following excerpt is taken from Chapter 5 - Microsoft SQL Server 2000
Security of Microsoft SQL Server 2000 Administrator's Pocket Consultant (ISB
N
0-7356-1129-7):
public is the default role for all database users. Users inherit the
permissions and privileges of the public role, and this role represents
their minimum permissions and privileges. Any role that you assign to a
user, beyond the public role, add permissions and privileges. If you want
all database users to have specific permissions, assign the permissions to
the public role.
The following excerpt is taken from Chapter 8 - Managing SQL Serer 2005
Security of Microsoft SQL Server 2005 Administrator's Pocket Consultant:
The guest user is a special user that you can add to a database to allow
anyone with a valid SQL Server login to access the database... Before using
the guest user, you should not the following information about the account:
The guest user is a member of the public server role and inherits the
permissions of this role.
The guest user must exist in a database before anyone can access it as a
guest.
The guest user is used only when a user account has access to SQL Server
but does not have access to the database through this user account.
Other topics around public server role that may be of interest include:
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
http://www.microsoft.com/technet/pr...n/sp3sec01.mspx
http://www.microsoft.com/technet/pr...ploy/mysql.mspx
10 Steps to help Secure SQL Server 2000
https://www.microsoft.com/sql/prodi...n/sp3sec04.mspx
SQL Server 2000 SP3 Security Features and Best Practices: Implementation of
Server Level Security and Object Level Security
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
SQL Server 2005 (BOL) - Security Considerations for a SQL Server Installatio
n
http://msdn2.microsoft.com/en-us/library/ms144228.aspx
Regards,
Keith Wilson
Disclaimer: this posting is provided "as is" without implied or express
warranties.
"Tushar" wrote:
>
>
Subscribe to:
Posts (Atom)