Getting Problem In Giving Database Permission To Group User
Jun 18, 2007
hi ,
I am getting some problem in using group in sql server login.
i have two NT groups in windows.i) developer and tester
I added a user Lalit in both gruop. I want to give db_owner permission on a particular database(Employee) to developer group and data_denyreader and deny_writer on same database(Employee) to tester group.
Now when i logging in to sql server using Lalit(windows authentication) iam not able to access the database(Employee).
Now my concern is that Lalit should have full access to the employee database when entered as developer user and limited access to employee database when entered as tester user.
i have a user database called calls. In that database there are about 100 of tables. now i want to give permission to that tables to a new user so what is the easiest way to give permission in a single shot to the 100 of the tables.
I know how to give the object permission to the table. But for 100 talbes it will take a lot of times , isn't it???
I've been looking through permissions to see if it's possible to grant a user permission to see only certain records from a table.
For example:
I'm granting users to view records in the table 'Sales', but I only want the users to see their respective data.
User A should only be able to see Sales where Sales.Location = 1
User B should only be able to see Sales where Sales.Location = 2
... and so on.
I believe I've read this is possible, but in looking through the permissions you can define, I've only found where you can limit the columns a user sees, not records.
Anyone have any ideas or did I just remember something wrong?
I've been assigned the task of setting up access to our SQL Server 2005 box. A consultant developing for us has accessing to 2 databases and I've set this up fine. It appears however that one of these databases is re-copied over to the server every night to keep data reasonably current.
I'm not interesting in changing this method as I'm not the maintainer (as yet).
Basically I would like to know if I've setup access to this database (it works fine), when the database is updated (with an SSIS package) the account seems to get deleted. Do the original permissions from the source database overwrite those of its destination?
I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
Tools -> connect to database -> Select datasourse - Microsoft SQL Server (SqlClient) -> Server name - comp4_8SQLExpress -> Log on to the server €“ Use Windows Authentication -> Connect to a database -> Select or enter a database name €“ MyDataBase
comp4_8 - is the name of my computer. I am using Windows XP Professional SP2. Login as administrator without password.
Now about the problem I have. On local machine everything is great! But I hosted my website and got this error. The question is how to give permission to this user to connect to my database. Cannot open database "MyDataBase" requested by the login. The login failed. Login failed for user 'ANGELINAASPNET'.
in SQL server 2005, Database User's permission will be overriden by the database Role's permission or ottherwise? For example, a userA is owner of table AA so it has all permisions on table AA but the user is a member of GroupB but group B has no permission to access to Table AA. What happen on User A?. has it permission to access to table BB or not? How can I find document or example about this? Please help me, thanks so much
Hi All, I need to give access to one user only to truncate a particular table. I am not able to frame exact query for this. However i can user EM and do it. But i wanted to know the query for this.
Hello All,I tried to set the access permissions for debugging stored procedure by reading the articlehttp://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspxandhttp://technet.microsoft.com/en-us/library/ms164014.aspxI have tried to add the role to sysaminas follows1)SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_sdidebug'(to find the sp)Error:--The stored procedure not found2)sp_addsrvrolemember 'Developmentswati.jain', 'sysadmin' though this is executed successfuly . Error is still persisting Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have permission to execute master.sp_sdidebug.
I have created a user and given select permissions on a table, I want to go deeper and just give select on a few columns within the table but unable to do so. Can someone tell me how I can do this.
SQL Server Security is not my strong point so forgive me for asking stupid questions.
I have a bunch of tables and sprocs within a schema 'MySchema'. I have a user 'MyUser' defined in the database.
I would like to give MyUser permission to SELECT from tables and EXECUTE all sprocs in MySchema. What is the simplest way of doing that? Will the following:
GRANT EXECUTE ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION AS [db_owner] GRANT SELECT ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION
accomplish that? (I can't test it out at the moment because our DBA isn't around and I don't have permission)
With best practices in mind - is what I am doing here considered "ok". Any suggestions/comments are welcome.
-Jamie
P.S. Can anyone recommend any documentation that talks about what best practices should be in the use of schemas. BOL is a bit sparse. Thanks.
Is it possible for a batch command to give access or add a new role for the user to database? Basically:
USE my_Database CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA= [dbo]; EXEC sp_addrolemember 'Application1', 'User1' EXEC sp_addrolemember 'SQL_Read_Only', 'User1'
If User1 was already created then ignore that and run the exec sp_addrolemember. Next,Â
The requirement is to customize database admin activities by creating new user group.
Need to create a group of user / dbauser1 which will have restriction in seeing the data but they should be able to alter database - add / remove the data file , increase or decrease the data file space when required.
This requirement came we wanted to create a new dba group they should not be able to any user data / any table but increase / decrease / add / modify space etc.
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2] SELECT 'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]' FROM sys.views;
I get an error dialog when I try to create a new SQL database, both via the Add New Item dialog and the property wizard of a new SqlDataSource control. The error is:
Local Database File:
User does not have permission to perform this action.
I've searched for help with this.
I ensured the App_Data folder exists and I added the local ASP.NET account to the group that have R/W access to it (although the RO flag is in an unchangeable tri-state on the folder). The SQL Server Express error log is clean and indicates full functionality. Everything is running locally. No VWD installation errors.
When I click on the properties of a 2005 sql server database and thenpermissions.If I select a group and then effective permission, I got an error,saying that "cannot execute as the principal server because the 'xxxgroup' does not exist, this type of principal cannot be impersonated,or you do not have permission (Microsoft SQL Server; Error: 15406)"I guess the error is valid, since the group does not exist in thedatabase as a user. If so, how do I get the effective permissions of agroup?
I'm fairly new to the world of SQL security (primarily a VB developer) and have a couple of questions/issues to resolve: The error I'm getting in my app is "the execute permission was denied on the object 'MySP'...schema 'dbo'". In essence I cannot execute any stored procedures when logged on as a non-admin user (all this was of course fine in my development environment, now I'm hitting the security issues!). My security is set up as follows:
Using Windows Authentication
I have an Active Directory Group "Group1", to which my non-admin user "User1" belongs. In SQL Mgt Studio I created a login for Group1 under Security/Logins I then created a user under MyDatabase/Security/Users, called "MyDbUser1". This uses the login above. All SPs are owned by dboSo what am I missing?
I have a quick question regarding getting data from a SQL database but I am slightly confused as to the best way to handle it.
Basically on of the projects I am working on I need to send data to another company, there are several calls required to the database to bring back various options for changing the questions asked on the front end.
Up to now all that has happened is there has been a mutual agreement between myself and this other company and they have just had access to the database to call a series of stored procs which I have written for them to access the data. Recently however the situation has changed and my client wants me stop them accessing the database however they still need to recieve the information from the database they recieve now.
What will be the best way to handle this? My knowledge of SQL is farily limited presently and I am only ust getting into learning a lot more about it.
Greetings,VWD EE and other Tools do not have problems working O.K. on my machine, when I am logged-on as restricted(limited) user, both environment and local web server are functioning, and it was MS effort to do it right.BUT the Problem is, when I try to connect to database->new DB connection(either through vwd or management studio etc). I get an Error - which should be written to event log (according to msg) but it isn't - that I can't connect. I am sure that it is because NO SQL SERVER2005EXPRESS instance is running, which I think is the only prerequisite to have it work (I do not need sqlbrowser service I am doint local development).SQL2005express service is configured on MANUAL start. I can start it as administrator through SQL config manager, but it is not convienient and what I want. I need to start it ONLY as a developer user, I do not want it to be running all-the-time for everyuser using computer. It was congigured as Network Service logon, I tried Local Service logon, and I even tried configuring it to logon as -my developer user- account, e.g. with limited user name and his password.In every case I can't start the service as member of users group and this developer. Then I added limited user to SQL2005EXPRESS group. Still NO help, won't start either.My question is, If I want to start developing ADO2.NET application and I need to have running SQL2005Express instance as a develper, how can I start it? I think I do not have some rights to masterDB or something. Do it allways need to be running when computer starts? Isn't there any other way, to start it JUST when I think I start developing?I know of user-mode of accessing SQL2005express DBs, but it also assumes that SQL2005Express service is already running, what I am trying to prevent. I do not want to have it running for everyone who uses computer, just for someone who neeeds it.Any help explaining me the right way HOW TO SETUP WORKING ENVIRONMENT when developing as limited user welcome, I read the the documentations and haven't found the answers.THANK YOU !
I grant read/update/insert permission on the speicific columns of the table on the users. I want to write the T-SQL to retrieve what permission/column permission does the user have.
I am new to reporting service. I set up a couple reports and deployed them to Report Manager. I then add my colleague's Windows user account to the report folder so she can view the reports. When she tried she got Login failed error. I have to go into the SQL database and manually add her account into the Login and assign permission to her for each database or stored procedure involved. Then she can view the reports. We do want to control who can view what reports and later hopefully who can view which part of a report. But do I have to manually give permission to each user account/or group in the SQL server? There got to be a better way to do this I feel. Can we use just one account to talk to the database and let what we set in report manager control who can view what? Thank you very much for your help in advance.
since the last upgrade SQL 2000 --> 2005, the biggest database I am working on (1.4 GB, with loads of binary data) keeps freezin at least one time per week. When I say 'freezin' I mean that you cannot query some of the tables as it gives a timeout error when trying.
I am not a DBA and I don't have any idea of what to do, the only thing which comes to my mind is that this database is too big, as if I go to the preperties window it says Space Available: 38 MB (what's that? there's really only 38 MB on this DB?). I tried re-writing all mainteinment plans (full backup every night, log backup every hour), but I realized that the database keeps freezing (and the backup jobs keep running untill you stop them).
I need to run a query that would return all users with sa privileges at a server level. Sysusers has the db users for each individual databaase. But I need to list all sa users and the databases they have rights to. Looks like I may need a cursor...not sure. If any of you can help me build this query, I would really appreciate it. Thanks.
I have a nightly job that restores a production database to a reporting server. I have some report- related stored procedures that need to run on the reporting server once the database is restored. However, during the restore, the permissions for the reporting stored procs get deleted and replaced with the production permissions, which are useless in this case.
Is there a script or system stored procedure I can schedule to run after the nightly restore that will also restore user permissions? Thanks, Frank.
Hi, I newly created one database (using creat database testdb ). After that i created login name and password for that database ( using create login login1 with password = 'pass1'; use testdb; command) and i created user for that login name ( using create user user1 for login login1 command). Then i connected testdb database using login1. But when i trying to create table in that database, it thrown error. Anyone please tell me that how to assign all privileges to the user user1?
I'm working in a team, and need to share a database between the group - we don't have the luxury of sharing a server and connecting to it.
So What we'd like to do is make a copy of the database schema, and then share that through our repository.
I figured I just need to copy the .mdf file from the sql server database folder, and put it in the visual studio directory to work on it?
So I've tried this, but when I try to create a TableAdapter to the database through visual studio, It gives me an "access denied". I figure this is probably something to do with security settings?
Do you think I'll need to change my Database connection from windows authentication to sql authentication?
I don't really know exactly how to do what I'm trying to do, so any help will be appreciated.
I setup SQL Server 2012 on Windows Server 2012 with the service accounts in the local Administrator group, but now that I'd like to remove the accounts from this group I'm finding they don't have the appropriate access to the network storage. notes on setting the per-service SID's for SQL (SQL Engine, Analysis Services, Reporting Services, and Agent Service) so they can read the Data, Log, and TempDB mount points?
Hi,I have installed sql server 2005 express on a Windows xp system with windows authentification. I did this with my administrator account (dan). When i start sql server management studio, i see: user name: myserverdan .Now i want to create a new login, so i rightclick on 'logins' in 'Security' of 'Database'. I take an existing windows-account, but when i click on OK, i get the error: user does not have permission to perform this action (error 15247).I also tried to delete a login, but same error.Any help would be appreciatedT.
