Posts

Showing posts from April, 2009

Deny table Permission at Table Level:

Deny table Permission at Table Level: 1)Connect SQL Server through windows authentication at Sysadmin Level 2)Under Security Find Logins Eg: Under my Security there are logins like Prasad,Rameh,Siva 3)Now i decided I want to give Deny Table permission to Siva named Login 4)Now Click on Siva Login Properties->Go to Server Roles->Give Public->Don’t give any other Server roles. And especially Don’t give sysadmin server role and at the same time he should not be the owner of that database(db_owner) 5)Next open Query Analyzer run the below Script. use MCTS GO GRANT INSERT TO Siva GO use MCTS GO GRANT SELECT TO Siva GO use MCTS GO GRANT UPDATE TO Siva GO use MCTS GO DENY DELETE TO Siva--This Stops Dropping and Deleting 6) After running the query If siva next time login by using his credentials he can not run delete script. ----CREATING A ROLE AND ASSIGNING MEMBER TO IT. Here i am creating a database role which will not allow users to take a specific datab...

ALTER SCHEMA

In Adventure Works Sample database which is in SQL Server 2005 contains tables like HumanResources.Employee HumanResouces.Address Person.Address If we run the These tables without giving schema name we can not get the result. For Eg:SELECT * FROM Employee--Here we wont get results because we are not giving schema name here. If we write query then only we will get the result like SELECT * FROM HumanResources.Employee--We can get result here. Now i want to change the schema from HumanResources to dbo so that i can get the result without mentioning the Schema name before the table ALTER SCHEMA dbo TRANSFER HumanResources.Employee This statement changes Table from HumanResources.Employee to Employee. And we can get the result.

WITH NOCHECK/CHECK

You can add or enable the constraint without checking existing rows,but the constraint will be marked as not trusted. If you try to add or enable a constraint checking existing rows, and there is a row breaking the constraint,then you will not be able to add or enable the Constraint The below Example gives the Scenario: CREATE TABLE Books (Bookid INT IDENTITY(1,1) PRIMARY KEY,[Name] VARCHAR(25)) GO CREATE TABLE Authors (Sno INT IDENTITY(100,1),AuthorName VARCHAR(25),BookID INT) ---At this sage there no foreign key relation specified. INSERT Books ([Name]) VALUES('Ten Commandments') GO INSERT Authors (AuthorName,BookID) VALUES('Sydney',10)---Data is inserted in both the Table.No relations defined so far. ----Now i want to define the relation based on Existing Tables and Data First i ran this query to Create Foreign Key Relation. ALTER TABLE Authors ADD CONSTRAINT FK_Books_Bookid FOREIGN KEY(BookID) REFERENCES Books(BookID)--This is Giving me Error because alreay Data is ...

Getting the tables in all the databases at one time.

By running the below system stored procedure we can get the tables in all the database in the server sp_msforeachdb @command1='USE ?;SELECT * FROM sys.Tables'

Agent XPs Disabled

By running the Below query we can Enable Agent XP Enable: sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE