Hi ,
I have a bunch of Users about 150 of them. I think they can't even run a SELECT .
When they try to run a select they get error messages :
"SELECT permission denied on object 'Object name', database 'Db_name', owner 'dbo'."
I have check out every thing !! All these Users are the members of public role on the database !!
They work fine on other Db's with the same settings !!
Not Sure what to do
PLEASE HELPP !!!
Is there any easy way to move users between SQL Servers for a particular database?
We have tests and development servers I would like to make sure the user lists are in sync for that db.
Thanks
Hi there!
I'm developing an application and i need to do the next thing:
I need to make a process, but this process just need to be done, when nobody else is modyfing datas in the database.
Is there anyway i could check this? I have read about the sp_who stored procedure but this one return all the activity in all the databases, and i just need to check in one specifically. And there's another issue, we all access with the user dbo to the database...
Any help will be appreciated..
I restored a copy of a database onto a new server. I created the proper logins on the new server prior to the restore. The users and permissions should all be identical. They appear to be in the system tables, but none of the users or permissions appear in the appropriate screens on Enterprise Manager.
Also, even though the permissions appear to be intact in the syspermissions table, when I connect as a certain user and try to query a table the user should have permissions on, I'm denied access.
Does anyone have any suggestions?
Thanks in advance!
Lisa Rae
Is there an SP that can tell the database, the users with access to the database, and their permissions? Meaning the result would be something like...
<database> <username> db_datawriter
<database> <username> db_datareader
<database> <usernameII> db_owner
Thanks in advance
-Kyle
Hi,
I want to create a new user for my database and allow them to only select data from the tables...
CREATE LOGIN NEHardcoreWITH PASSWORD = 'abc'USE aiaccontentdb;CREATE USER NEHardcore FOR LOGIN NEHardcore
What permissions will this give to the new user? Do I need to grant any permissions and/or revoke any?
Thanks!
I am trying to create a new user for a SQL Server database and use the credentials in an
ASP.NET app.
Problem is dbo permissions are not being applied to the database objects when I set up a
new user (Logins -> New Logins) with SQL Server Authentication, set the default database to
the database I want.
I then get an error message saying that the user has not been granted access to their default
database : DBNAME and therefore will not be able to gain access to their default database.
I'm not sure why this is as I am logged in as sa.
Anyway, when I ignore the error and set-up this new user through the Users section of the
database none of the dbo permissions carry over.
Can anyone help?
Cheers.
I have used DTS to imports users from one database(a) to another(b)
DTS run fine, but the original permissions don't were filled.
Can someone explain How I can do it?
Luiz Lucasi
Rio de Janeiro - RJ
Hi,
did anybody encounter or write a stored procedure or a tool that sets all permissions on all users on a given database?
It is a major headache to set all permissions again after some changes with a tool like ErWin and then adding some users.
Kind regards,
Andreas Erben
hi, please anybody help me with this....
i need to have a user account with the following requirements:
a. can create/alter/drop tables/views that is created by this user;
b. can read/update/delete records from tables created by this user
c. can read/add records to tables created by other user
d. can create/edit/execute stored procedures
is this possible? how can i do this? how can i allow a user to create new objects or alter objects owned by him at the same time prevent him from dropping objects created by other user?
how can i give a user full access to objects that he created and limit his access rights like dropping objects, deleting records or updating records from tables that is not created by him?
can i do these thru enterprise manager or do i have to run some scripts in order for these to happen? if so, can somebody help me with the scripts or links that i can use for references?
thanks a lot! hope you can help me with these...
thanks a lot...
I have a user that I have granted the server role "securityadministrator" and a database role "db_securityadmin". When logged inas this user I can create new logins but not run sp_adduser to add thenew login to as this says I don't have permission to do this. I canhowever run sp_revokedbaccess to get rid of a user from the database.
View 2 Replies View RelatedHi Everybody,
I am working on SQL 7.0/2000. I have given lot of permissions to the user 'duser1'. The permissions like select,etc..,create... Now I want to give the same permissions(what I have given to 'duser1') to the other user called 'duser2'. Right now I hvn't created any Database Roles or Server Roles.
Do we have any easy method to copy the permissions of one user to the other user?, like script generation or any method. If anybody knows that please guide me.
tks in advance,
Sam
I have an archival process on a large database that runs once a month.At the beginning of the process the triggers and indexes on thetables whose data is moved are dropped, the data is moved and then thetriggers and indexes are recreated at the end. This produces amassive improvement in performance.The problem is the process is supposed to run on users accounts (thatsthe way the front-end is set up) and they don't have the neccessarypermissions to drop & create triggers & indexes. I can't see any wayto give them permissions only on specific tables or triggers/indexes.Nor does giving them permissions to the stored procedures that do thedropping & re-creating work, DDL permissions don't seem to beinherited the way they are with tables.Is blanket rights to drop & create objects through the db_ddladminrole the only way users can get rights?Thanks,K Finegan
View 2 Replies View RelatedAs our customers demand that we tighten our IT security in the company,I've been asked to prepare a report quarterly showing, for each user inActive directory, what his effective permissions are for every table inevery database that he has permission for on our SQL Server 2000 server. Isearched a bit for a tool to do this, but all I found was the PERMISSIONS()function for showing effective permissions of the current user. Is thereany way to do it for an arbitrary user, without logging in as them?
View 5 Replies View RelatedI have looked and looked and looked and can not find an answer to this simple question. I want a way to script out all the permissions for a given user.
I have a user (User1) and he has the following permissions lets say:
Server
- View Server Activity
Database1
- CREATE AGGREGATE
- CREATE DEFAULT
- CREATE FUNCTION
- CREATE PROCEDURE
- CREATE RULE
- CREATE SYNONYM
- CREATE TABLE
- CREATE TYPE
- CREATE VIEW
- SHOWPLAN
- VIEW DEFINITION
Schema1
- SELECT
- INSERT
- DELETE
Schema2
- SELECT
- EXECUTE
Objects
- SELECT ON schema3.table1
- EXECUTE on schema4.storedproc1
I need a something (script, report, voodoo magic spell) that will show me this. It is hard to believe that this is not a built in report with SQL2005. It was hard to do in 2000 but you could do it if you played around with the scripting options enough. I can not find a clear cut way of doing this in 2005 to save my life. Maybe I am just blind, but my fellow DBA's have the same problem.
How can I do this? Please???
Jim Youmans
St Louis
I need to be able to set up a table listing users permissions, this needs to be run daily and then notify me of any changes without using the auditing or profiler software.
Any ideas?? :eek: :eek: :confused:
Hello all,I am looking for the script, which I believe exists already.I need tobe able to populate the script for security of one database andapply it to another database, even if it is located on another server:1. All logins which not exist have to be created and which existsignored including the NT accounts2. Users same as the old database + the existing ones stay in database3. Passwords for the new logins.4. All permissions/grants on all objects for the users that exists(usually it's the case) and ignore those that don't.I have script which does some of it, but it's not perfect, so everytime there are some errors.Please let me know, if you need me to email script that I have. It'spretty long so I cannot just post it in here.Thank you in advance.
View 3 Replies View RelatedI am in need of a utility (stored procedure, third party app, etc)that will help streamline the process of managing and auditing objectpermissions, users and roles on multiple sql server 2000 instanceswith multiple databases on each instance. That's a quick overview ofwhat I need, do I need to be more specific?Thanks.
View 2 Replies View Related
Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.
Our company has 2 Database Roles (DBE and DBA). The DBE creates
database schema, performs SQL Server Administration, and manages server
security. The DBA writes data access, ETL, and manages database
security. In 2005, we're struggling with how to allow the DBA to see
all of the logins on the server in order to add them as users of their
database. What permissions does the DBA need to select from any of the
logins on the server to add them to their database?
Michelle
A question on the permissions hierarchy:
Since logins, database users, and database roles are both principals and securables - what does it mean to GRANT permission on a login/user/role to another principal? Does it mean that for a login - you can GRANT permission to EXECUTE AS that login or modify it, for example?
I have jsut started using SQL server 7 and am having problems with accounts permissions, users,roles, groups, owners etc what are the differences?
View 1 Replies View RelatedWe are currently running sql 2000 and are moving our database onto sql 2005 running on a different box.
We have managed to move the entire database, with users however the users permissions on specific tables/views/stored procedures have not been transferred, does anyone know a way of transferring user permissions rather then doing them all by hand?
The system is a large (over 500 table/views/stored procedures) and a very active one and therefore downtime is not optional.
any suggestions would be appreciated
Regards
Chris V
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
i need to change the object owner from actuate to dbo
i wrote this script which will give me an execute process which i just need to run on query analyser it works fine for object owner change from dbo to new user but gives an error saying the object does not exist when i want to change it from user actuate to dbo...can some one correct me in this regard..here is the script.
Select "Exec sp_changeobjectowner", + name + ",dbo"
From sysobjects
Where type = "P" and
uid = 6 order by name
uid i found from sysusers table
it gives me some thing like this
Result : Exec sp_changeobjectowner MonthlyPerformanceSummary_Occupancy,dbo
This works the other way around
Exec sp_changeobjectowner MonthlyPerformanceSummary_Occupancy,kavitha
error: object does not exist
And the other issue is i have certain stored procedures for which i need to give permission exec for a specific user, i did create the user...what shold i do to grant exec privilage to this user..for all the stored procedures at a time finding the stored procedure name.
its like this the name always starts with sp_actuate...does this help in finding the stored procedure and then granting the exec privilage.
Thanks a lot in advance.
Kavitha
I need to know how many concurrent users are allowed to access the database at any point of time.Will it depend on the type of licence?Where do we set this parameter.Some users are complaining 'time out' problem.Thanks for any help!
View 1 Replies View RelatedI restored a database from one full backup.The users are missing now in the new databse.When I try to create the same users again it says that they are already existing!Please help what shall I do to restore/recreate the users?Do I need to do anything with the Master?
View 4 Replies View RelatedHi,
I am having 10 tables in my database. Some times users are complianing that they are not getting permission to one particular table. I checked the table and the owner is dbo only. Why it is behaving like this. the users having all permissions on the database. Pls suggest me.
--thank u
--Rk
I made a backup of a production database and copied that backup over to a development server and restored the database. Now I have users saying that the application is failing on development. I have users that have NT authentication and some with SQL authentication.
What is the best way to get everything to sync up again?????
Hi all,
Is there any way to get the @@trancount for a connection from outside the connection?
The reason i'm asking is that a customer just lost a days work, and there is nothing in any table from a certain time onward. One theoy is that a backup was restored, but we checked and that is not the case.
So another theory is that a certain sproc began a transaction, but never finished because of an error. (We had some strange timeouts as well, so this is quite plausible.)
So the question: Can i get a list of current connections with their trancount? I could just run this to see if a certain connection would never get back to zero to check the transaction theory.
Thanks in advance,
Gert-Jan
I've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users. Any tips on how to best accomplish full database moves where data is owned by different security users?
thanks,
What kind of permissions do you need to be able to run a job created by another user or sa if you are not the job owner and don't have any sys admin priveldges??
View 1 Replies View RelatedHi
I'm trying to get the CustomSecurity sample in Reporting Services to work [C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesExtension SamplesFormsAuthentication SamplecsFormsAuthentication] but I keep on getting the following message:
The permissions granted to user 'Admin' are insufficient for performing this operation. (rsAccessDenied)
I have given <servername>ASPNET execute permissions.
I have successfully created a new user "Admin".
However, when I then try to logon to either ReportServer [http://miles/ReportServer/logon.aspx] or ReportManager [http://miles/Reports/pages/uilogon.aspx] I get the above error.
I'm using SQL 2005 Developer edition and XP professional.
Please help!